Previous | Contents | Next |
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:
' 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.
'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
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
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