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

  Home | About | Links | Contact | Excel | Access