DataWright Information Services

Consulting and Resources for Excel and Access

Using SQL with dates

If your regional settings are not the same as the US settings for
dates, you will most likely experience problems when you build SQL
expressions in VBA that use dates. This is because SQL requires
US date formats. The normal query builder does not have this
problem; evidently, it can use the regional settings.

To illustrate, the date #1/7/2007# is 1 July 2007 in Australian
regional settings. Use this in a SQL string that you build in VBA,
and it will be interpreted as 7 January 2007. However, #13/7/2007#
is correctly interpreted as 13 July 2007 — any date with a day
value >12 is OK. If you are trying to create queries in SQL, this
behaviour will most likely drive you nuts. Here are two workarounds
that solve the problem.

Use a custom function to reformat the date

Place this code in a new module. Save the module as basDates.

Function SQLDate(sDate) 
    If IsDate(sDate) Then 
        SQLDate = "#" & Format(sDate, "mm/dd/yyyy") & "#" 
    End If 
End Function

Let’s assume that you are filtering inspections between two
dates, whose values come from two text boxes called. txtFrom and
txtTo. You want to build a filter on the InspectionDate field, so
you are creating a filter to use as a parameter when frmInspections
is opened. This code snippet will do the job:

Private Sub cmdInspection_Click()
  Dim sWhere As String

  sWhere = "[InspectionDate] BETWEEN " & SQLDate(Me.txtFrom) & " AND " & SQLDate(txtTo)
  DoCmd.OpenForm "frmInspections", acNormal, , sWhere
End Sub

Convert the date to a Double

It turns out that converting dates to double-precision numbers
works just fine too. This code will also give the correct results:

Private Sub cmdInspection_Click()
  Dim sWhere As String

  sWhere = "[InspectionDate] BETWEEN " & CDbl(Me.txtFrom) & " AND " & CDbl(txtTo)
  DoCmd.OpenForm "frmInspections", acNormal, , sWhere
End Sub