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
