Access 97 Date Format
OK so in 2010 we shouldn’t still be dealing with Access 97 databases but in the real world some people still have them!
So a client tells me their Access 97 database form is showing them the wrong data for the date range they enter. The form is opened from a command button on another form where the user enters a date and this date is used as the link criteria to filter the main form.
I pulled up the query the form is using and it works fine when I enter a date. I get the date entered to pop up in a message box to check that it looks OK and it does then when I use the command button the data returned is wrong for the date entered.
I check the dates in the table are the correct format and that the regional settings of the PC are correct (set to UK).
After a little Googling it appears that in Access 97 VBA assumes automatically that all dates are in US #mm/dd/yyyy# format – which explains why the query worked fine but the command button (which filtered the form using VBA) didn’t work as it was converting the UK date entered to a US date and so returning the wrong records.
Luckily the fix wasn’t too hard. Use the format function to return the correct date if handling dates in VBA:
'Code Courtesy of
'Joe Foster
'
Global Const JetDateTimeFmt = "\#mm\/dd\/yyyy hh\:nn\:ss\#;;;\N\u\l\l"
Global Const JetDateFmt = "\#mm\/dd\/yyyy\#;;;\N\u\l\l"
Global Const JetTimeFmt = "\#hh\:nn\:ss\#;;;\N\u\l\l"
and then
SQL = SQL & "When >= " & Format$(Now, JetDateTimeFmt)
Many thanks to www.mvps.org for that one!
Leave a Reply
You must be logged in to post a comment.