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!