Date formats used by Access SQL

Regardless of the regional settings of your PC, you must provide dates in US format for SQL statements in VBA code. The Microsoft Knowledge Base entry at Q149095 ACC states:

When you use Visual Basic for Applications to compare dates in an SQL string, you must use a standard US date format, regardless of the Regional Settings in Control Panel.

We use this simple one-line function to convert a UK date into a US-format string that can be concatenated into an SQL Where clause:

Function SQLDate (ByVal MyDate As Variant) As String
   SQLDate = "#" & Format(MyDate,"mm/dd/yyyy") & "#"
End Function