The vagaries of dates in queries

Consider this situation - you've written a query that returns all records that fall within certain date parameters. When you run the query it returns the correct data, and everything is hunky-dory. Now let's say you're building the same query programmatically; first you have to remember that dates need to be contained within hash symbols in the same way that strings have to be in quotes, but that's not the real issue. If you're not from the USA, chances are you local date format will be something like dd/mm/yyyy, whereas it is mm/dd/yyyy in the States. Did you know that when you use VBA to concatenate dates into an SQL string, you must use a standard U.S. date format, regardless of your PC's regional settings.

Consider this example, in which a form contains a field Param for entering a date in, and a command button cmdOK which, when pressed, displays a count of staff who started work after the specified date. The code for the command button would be something like:

Sub cmdOK_Click()
    MsgBox DCount("*","tblStaff","StartDate>#" & Me.Param & "#")
End Sub

Looks okay doesn't it? But, at best, this will return the wrong results and, at worst, could crash your code. The date has to be parsed in US format, like this:

Sub cmdOK_Click()
    MsgBox DCount("*","tblStaff","StartDate>#" & Format(Me.Param, "mm/dd/yyyy") & "#")
End Sub

In the unlikely event that this still gives problems, use the following to completely negate the possibility of any ambiguity:

Sub cmdOK_Click()
    MsgBox DCount("*","tblStaff","StartDate>#" & Format(Me.Param, "dd-mmm-yyyy") & "#")
End Sub