Previous

Contents

Next

Formatting variables for SQL statements

Universal dates formatting for SQL statements

Access XP, 2000, 97, ...; VB 6, 5, ...

As you know, it is very useful to build SQL statements for recordsets dynamically in VB code. If some criteria is of Date type than you have to format it to look like: "#mm/dd/yy hh:nn:ss#". If Date or Time Style in your computer's Regional Settings (see Control Panel) differs from US format than you have some problems formatting Dates to build SQL statements. But when your user have different Regional settings than this is a real trouble.
This tip helps your to forget Dates formatting rules for SQL statements forever! Just use the function to format Dates:

Version 1 - simple and clear

' format Date/Time value for use in SQL sentences
' this format is for MS Access database
Public Function FDate(ByVal dt1 As Variant) As String
  On Error Resume Next
  Err.Clear
  FDate = "#" & Format(dt1, "mm\/dd\/yyyy hh\:nn\:ss") & "#"
  If Err.Number <> 0 Then FDate = "#01/01/1900#"
End Function

' format Date/Time value for use in SQL sentences
' this is MS SQL server specific format
Public Function FDateSql(ByVal dt1 As Variant) As String
  On Error Resume Next
  Err.Clear
  FDateSql = "'" & Format(dt1, "yyyy\-mm\-dd hh\:nn\:ss") & "'"
  If Err.Number <> 0 Then FDateSql = "'1900-01-01'"
End Function

As you can see, there are two different functions for MS Access (and any tables, linked to the MS Access database); and MS SQL Server (connected through ODBC, ADO, ...) databases.

Version 2 - more universal approach

'Database driver's IDs
Global Const gconDriverNone As Long = 0    ' no driver 
Global Const gconDriverJET As Long = 1     ' MS JET database ( .MDB file)
Global Const gconDriverODBC As Long = 2    ' MS SQL Server (ODBC driver, OLE DB ...)

' format Date/Time value for use in SQL statements
' lngDriver:
'  = gconDriverJET - MS Jet format
'  = other (default) - this is MS SQL server specific format
' blnZero2Null parameter allows format 'not dates' or 'empty dates' as 'Null'
'   If blnZero2Null = False (default): Output is January 1 1900
'   If blnZero2Null = True: Output is converted to 'Null'
Public Function FDateSql(ByVal dtm1 As Variant, Optional ByVal lngDriver As Long = gconDriverODBC, Optional ByVal blnZero2Null As Boolean = False) As String
Dim dtm2 As Date
Dim strOut As String
  On Error Resume Next
  Err.Clear
  dtm2 = dtm1
  If Err.Number <> 0 Then
    dtm2 = Empty
  End If
  If dtm2 = Empty Then
    If Not blnZero2Null Then
      dtm2 = DateSerial(1900, 1, 1)
    End If
  End If
  If dtm2 > Empty Then
    Err.Clear
    If lngDriver = gconDriverJET Then
      strOut = "#" & Format(dtm2, "mm\/dd\/yyyy hh\:nn\:ss") & "#"
    Else
      strOut = "'" & Format(dtm2, "yyyy\-mm\-dd hh\:nn\:ss") & "'"
    End If
    If Err.Number <> 0 Then
      strOut = ""
    End If
  End If
  If strOut = "" Then
    strOut = "Null"
  End If
  FDateSql = strOut
End Function

Universal numbers formatting for SQL statements

Access XP, 2000, 97, ...; VB 6, 5, ...

As in the previous tip, this method is useful to build SQL statements for recordsets dynamically in VB code. No matter of your computer's Regional Settings you'll get precise and valid format for numbers:

' Format numbers (integer or not) for SQL statements
' If number1 is not a number then it's converted to '0'
' blnZero2Null parameter allows replace '0' to 'Null' (e.g. for foreign keys)
Public Function FNumSql(ByVal number1 As Variant, Optional ByVal blnZero2Null As Boolean = False) As String
Dim decNum As Variant  ' Decimal is more precise than currency
Dim strOut As String
Dim lng1 As Long
  On Error Resume Next
  If IsNumeric(number1) Then
    decNum = CDec(number1)
    If decNum <> 0 Then
      If Int(decNum) = decNum Then
        strOut = Format(decNum, "0")
      Else
        strOut = Format(decNum, "0.\.0#######################")
        'find last '.' character - decimal symbol
        For lng1 = Len(strOut) To 3 Step -1
          If Mid(strOut, lng1, 1) = "." Then
            'cut out "localised" decimal symbol
            strOut = Mid(strOut, 1, lng1 - 2) & Mid(strOut, lng1)
            Exit For
          End If
        Next lng1
      End If
    End If
  End If
  If strOut = "" Then
    If blnZero2Null Then
      strOut = "Null"
    Else
      strOut = "0"
    End If
  End If
  FNumSql = strOut
End Function

Formatting example

And now you may see VB code, that uses formatting functions, mentioned above.

'Example 1 of Formatting variables for SQL statements
Public Function FormatSQLExample1() As Boolean
Dim strSql As String
Dim db1 As DAO.Database
Dim rst1 As DAO.Recordset
  
'These are two parameters, that you may change in code
Dim InventoryDate As Date
Dim StoreID As Long
  
  ' ...
  
  'Build SQL statement using two parameters
  strSql = "Select * From Inventory Where StoreID=" & FNumSql(StoreID) _
    & " And InventoryDate>" & FDate(InventoryDate)
  
  'Now you may use it to access data with recordset
  Set rst1 = db1.OpenRecordset(strSql, dbOpenDynaset)
  ' ...
  
  'Or you may change your ListBox contents
  '(Assuming this code is in Form Module and Me!List1 is ListBox on this form)
  Me!List1.RowSource = strSql
  ' ...
  
End Function

Author: Yuri Volkov
Last changed: 2002-11-12