Hello,
I’d like to repeat the last not = 0 record under certain conditions, in a table or in a query.
What I have:
Calendar_All Dates
Calendar date MyValue
7/6/2014 0.00
7/7/2014 108.94
7/8/2014 107.71
7/9/2014 107.07
7/10/2014 105.89
7/11/2014 105.30
7/12/2014 0.00
7/13/2014 0.00
7/14/2014 104.69
7/15/2014 0.00
7/16/2014 0.00
7/17/2014 0.00
7/18/2014 0.00
7/19/2014 0.00
7/20/2014 0.00
7/21/2014 0.00
7/22/2014 0.00
7/23/2014 0.00
What I want:
Calendar_All Dates
Calendar date MyValue
7/6/2014 0.00
7/7/2014 108.94
7/8/2014 107.71
7/9/2014 107.07
7/10/2014 105.89
7/11/2014 105.30
7/12/2014 0.00
7/13/2014 0.00
7/14/2014 104.69
7/15/2014 104.69
7/16/2014 104.69
7/17/2014 104.69
7/18/2014 104.69
7/19/2014 0.00
7/20/2014 0.00
7/21/2014 104.69
7/22/2014 104.69
7/23/2014 104.69
Basically, if Calendar date > Date(), if Calendar Date not Saturday or Sunday, weekday(Calendar date<>1 and <>7), AND Calendar Date not in (Holidays table)
Then repeat the last not = 0 value of MyValue
The idea is clear but I can’t find the right way to write it.. I thought of changing the default value but the value is already 0, while default is null + I need to set the default value under certain conditions.
Thanks a lot for your help!
Ben
8 1109
Ben,
I htink you have much of the basics to do what you want. How fluent are you in VBA and using recordsets? This would allow you to examine multiple aspects of each record.
There may be a way to do this with just a SQL SELECT Statement, but it would probably make my head hurt more than I care to guess.
Hi,
I'm starting to understand how to use them. I think I would be better to do this using VBA as well.
What are you thinking about?
Ben
Without writing your code for you, I would begin by opening a recordset based upon your table, sorted by date. Have a variable that slooks at the value of MyValue for the first record. Then cycle through records 2 - x. If the MyValue for that record is not 0, then you update the variable to reflect the new value. Then, if the date of the current record meets all your criteria, and is also 0, then update that record to reflect the value in your variable.
Relatively straightforward, and we will be glad to help you through any hitches you come across. We'd like to see what you come up with first.
I'd do something like: - Public Sub GetInterestingValue()
-
-
Dim x As Double
-
Dim recordSetDate As DAO.Recordset
-
Set recordSetDate = CurrentDb.OpenRecordset("Calendar_All Dates")
-
-
'I need to like "scan" all the records, which I don't know how to do, for recordSetDate in [Calendar_All Dates], while maybe?
-
-
If recordSetDate("MyValue").Value <> 0
-
-
'{This will be for later: AND Weekday(recordSetDate("Calendar Date").Value) Between 2 and 6 AND recordSetDate("Calendar Date") is in [UK_Holidays]}
-
-
then
-
x=recordSetDate("MyValue").Value
-
-
'Go scan Next one
-
-
End Sub
-
-
-
So it should return the last value with the goo syntax right?
-
-
After I see something like
-
-
Public Sub FillTheTable()
-
-
Dim recordSetDate As DAO.Recordset
-
Set recordSetDate = CurrentDb.OpenRecordset("Calendar_All Dates")
-
-
'I need again to like "scan" all the records from the last corresponding to the x I got earlier to the last of the table, which I don't know how to do, for recordSetDate in [Calendar_All Dates], while maybe?
-
-
For recordSetDate("Calendar Date") > {Calendar Date corresponding to the x calculated earlier}
-
-
If Weekday(recordSetDate("Calendar Date").Value) Between 2 and 6 AND recordSetDate("Calendar Date") is not in [UK_Holidays]
-
-
then
-
recordSetDate("MyValue").Value=x
-
-
'Go scan Next one
-
-
End Sub
x is not global I guess, I should call GetInterestingValue() and store it as x at the beginning of the Second sub
Good first trial for a rookie(?). Let's use what you have so far and work on it:
P.S. Please use Code Tags when posting code. - Public Sub GetInterestingValue()
-
Dim dblCurrentValue As Double
-
Dim strSQL As String
-
Dim recordSetDate As DAO.Recordset
-
-
'We want to make sure we sort by the date:
-
strSQL = "SELECT * FROM [Calendar_All Dates] " & _
-
"ORDER BY [Calendar Date];"
-
'You may notice the challenge of having spaces in your
-
'field names here--try to avoid in the future
-
-
Set recordSetDate = CurrentDb.OpenRecordset(strSQL)
-
-
'This should return all records in the Table
-
'But if something goes wrong, we want to make sure
-
'there are records before we start:
-
If Not recordSetDate.RecordCount = 0 Then
-
'First find out what the MyValue is
-
recordSetDate.MoveFirst
-
dblCurrentValue = recordSetDate!MyValue
-
recordSetDate.MoveNext
-
Do While Not recordSetDate.EOF
-
'Perform your data validations here
-
'for weekdays and holidays
-
-
-
'Update your Current Value here (when needed):
-
dblCurrentValue = recordSetDate!MyValue
-
-
'If you need to assign the value of the MyValue
-
'This will probably be nested in an If...Then
-
With recordSetDate
-
.Edit
-
!MyValue = dblCurrentValue
-
.Update
-
End With
-
Loop
-
End If
-
End Sub
You were not far off, but I htink it's a lack of experience. We will be glad to help you through the rest of the process as you trudge along--we've all been there at some point in our lives.
This is just beautiful, thanks a lot for your help.
I will try to write in properly tomorrow and I will tell you about.
Many thanks again
There is a SQL only solution but it's convoluted to say the least.
Please note this solution was created and tested in SQL Server, some minor syntax changes would be needed to make it work in Access: - SELECT
-
y.dt,
-
z.amt
-
-
FROM
-
(
-
SELECT
-
t1.dt,
-
MAX(COALESCE(t2.dt, t1.dt)) AS mdt
-
-
FROM
-
#t AS t1
-
-
LEFT JOIN #t AS t2
-
ON t1.dt > t2.dt AND
-
DATEPART(WEEKDAY, t1.dt) BETWEEN 2 AND 6 AND
-
DATEPART(WEEKDAY, t2.dt) BETWEEN 2 AND 6 AND
-
t1.amt = 0 AND
-
t2.amt <> 0
-
-
GROUP BY
-
t1.dt
-
) x
-
-
INNER JOIN #t AS y
-
ON x.dt = y.dt
-
-
INNER JOIN #t AS z
-
ON x.mdt = z.dt
-
-
ORDER BY
-
y.dt
Basically you have to reference the table 4 times to make it work.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Daniel Ng |
last post by:
Thanks for the 2 guys how hep to to solve to count the number of recount
in forum. I'm done wif that. Got 1 more question..
Example
Record 1: ID 2 VALUE 2
Record 2: ID 3 VALUE 3
Record 3: ID 2...
|
by: Lucius |
last post by:
Hello everyone, I have a query problem.
I'll put it like this. There is a 'publishers' table, and there is a
'titles' table. Publishers publish titles (of course). Now I want to make a
query (in...
|
by: travismorien |
last post by:
I have four tables of different "entities". One table contains
information for "people", one for "trusts", one for "companies" and one
for "self managed super funds". Each type of entity has an...
|
by: RayPower |
last post by:
I'm having problem with using DAO recordset to append record into a
table and subsequent code to update other tables in a transaction. The
MDB is Access 2000 with the latest service pack of JET 4....
|
by: pranjalraje |
last post by:
can you please help me with sql querry for 'finding last record in a table'
|
by: chris |
last post by:
I have a Switchboard I created that has the program version at the
bottom. I have a table with version history in it that I maintain
everytime I make changes and release it to the users. ...
|
by: sheenaa |
last post by:
Hi,
I want to display the last recorded record in the database of SQL SERVER 2005.
How can i display it with the select query in the grid view...
The database contains the...
|
by: JohnG1234 |
last post by:
I am new to SQL and just started making reports in reporting services, need help on how to write a query in SSRS 2005 dataset
Here is an example
Table A (3 columns Date , Limit, Product)
...
|
by: psyvanz |
last post by:
here is the code im working with...
hope you can understand? cause this thing searches the first one, not the last one record...
i wanted ONLY to find the last record in a table...
Please...
|
by: PAuser |
last post by:
Greetings,
I`m having a curious situation in MS Access 2003 while editing records in a cross table query.
Tables are:
Table1 (field1_1, field1_2, field1_3)
Table2 (field2_1, field2_2,...
|
by: DolphinDB |
last post by:
Tired of spending countless mintues downsampling your data? Look no further!
In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
by: Shællîpôpï 09 |
last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
|
by: af34tf |
last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
| |