Date comparisons aaarrrggghhhh!!!!

[ghalewood]

I have the following code which is creating records into a table to 7 days in the future. The code gets todays date, checks the latest date on the table and creates the relevant records. I know that the price_history!date_entered field is defined as a short date but the highlighted line does not retrieve any records and I have no idea why, everything seems ok to me. p.s. I really, really hate date fields (not the first time I have had problems).

Thanks
Graham

Sub maintainpricehistory()

Dim myDB As Database
Dim rsPricehistoryread As Recordset
Dim rsPricehistoryone As Recordset
Dim rsPricehistoryadd As Recordset
Dim rsLastrun As Recordset
Dim vaCount As Long
Dim vaMydate As Date
Dim vaDateto As Date
Dim vaDate As Date

Set myDB = CurrentDb()

Set rsPricehistoryadd = myDB.OpenRecordset("price_history&quot


vaMydate = Format(Date, "short date&quot
vaDateto = vaMydate + 7

' check for multiple runs in a day
Set rsLastrun = myDB.OpenRecordset("SELECT * FROM [parameters]&quot

If rsLastrun!pricehistory_date = vaMydate Then
GoTo endofproc
End If

'add records for 7 days in advance

' select highest date on table
Set rsPricehistoryone = myDB.OpenRecordset("SELECT * FROM [price_history] _ ORDER BY [date_entered] DESC ", dbOpenDynaset, dbReadOnly)
rsPricehistoryone.MoveFirst
' find difference between now and highest date
vaCount = DateDiff("d", rsPricehistoryone!Date_entered, vaDateto)
vaMydate = Format(rsPricehistoryone!Date_entered, "short date&quot



' select all records for basis of new ones to be entered
Set rsPricehistoryread = myDB.OpenRecordset("SELECT * FROM [price_history] _ WHERE [date_entered] = " & vaMydate & "", dbOpenDynaset, dbReadOnly)
rsPricehistoryread.MoveLast ' populate recordset
rsPricehistoryread.MoveFirst

Do Until vaCount = 0

vaMydate = vaMydate + vaCount

Do Until rsPricehistoryread.EOF
rsPricehistoryadd.AddNew
rsPricehistoryadd!Site_id = rsPricehistoryread!Site_id
rsPricehistoryadd!Date_entered = Format(vaMydate, "DD/MM/YYYY&quot
rsPricehistoryadd!Leaded_price = rsPricehistoryread!Leaded_price
rsPricehistoryadd!Unleaded_price = rsPricehistoryread!Unleaded_price
rsPricehistoryadd!super_price = rsPricehistoryread!super_price
rsPricehistoryadd!Derv_price = rsPricehistoryread!Derv_price
rsPricehistoryadd!type5_price = rsPricehistoryread!type5_price
rsPricehistoryadd!type6_price = rsPricehistoryread!type6_price
rsPricehistoryadd.Update
rsPricehistoryread.MoveNext
Loop

vaCount = vaCount - 1
rsPricehistoryread.MoveFirst

Loop

rsLastrun.Edit
rsLastrun!pricehistory_date = vaMydate
rsLastrun.Update

endofproc:


Set myDB = Nothing
Set rsPricehistoryread = Nothing
Set rsPricehistoryadd = Nothing
Set rsPricehistoryone = Nothing
Set rsLastrun = Nothing

End Sub

 

[vb5prgrmr]

If MSAccess then surround the date with the # symbol. If SQL then single ticks/quotes '.
[tt]
Set rsPricehistoryread = myDB.OpenRecordset("SELECT * FROM [price_history] WHERE [date_entered] = #" & vaMydate & "#", dbOpenDynaset, dbReadOnly)
[/tt]

And you are not the only one to have problems with dates especially converting code from access to sql server (Imaging going through a large program to update # to '.). I now (in some situations) save my dates as a double (in access)yyyymmdd=20021223 and save my time in another field if I need the time and I can then upsize to sql without having to change that part of my code.

I hope this helps, Good Luck

 

[danvlas]

Just in case you use a system date format different from mm/dd/yyyy:

WHERE [date_entered] = #" & Format(vaMydate,"mm/dd/yyyy&quot & "#"

will spare you from getting wrong results (10/05/2002 could mean 10 May 2002 according to your system date format, but the SQL will evaluate it as 05 Oct 2002)

Happy Holidays!

Daniel Vlas
Systems Consultant
danvlas@yahoo.com

 

[VBAjedi]

I'm in a hurry, so I didn't analyze your code to see exactly how you'd use this, but:

Another function that I use a lot to resolve date-matching issues is the CDate(AnyString) function. It converts a string to a date format that you can match against. It has saved me often!



Hope this helps!

VBAjedi

 

[ghalewood]

Thank you everyone. The use of '#' seems to have made the comparisons work correctly