Forms: Use Variables in SQL behind forms

(Q) In code behind my form, I'm trying to reference the value stored in a control on my form in a SQL statement. But I'm not successful in doing this. I'm using the following syntax:

    strSQL="Select * from Employees where [LastName] = forms!myForm!myControl"

What am I doing wrong??

(A) In order to reference values contained in form controls, you have to use the & operator and concatenate the results. The positioning of quotes is also important. For example:

- If the control contains a numeric value (example, an ID Field),
    strSQL = "Select [LastName] from Employees where [EmpID]=" & me!EmpIDControl

- If the Control contains a date value,
    strSQL = "Select * from Employees where [EmpStartDate] < #" & _
                        me!EmpStartDate & "#"

Note: **Posted by Alex Dybenko**

A recommended approach is to use

    [EmpStartDate] < " & format(me!EmpStartDate,"0")

because this ## structure depends on regional settings and may cause error on some PCs.

- If the control contains a string value,
    const cQUOTE = """" 'Thats 2 quotes in sequence
    strSQL = "Select * from Employees where [LastName]=" & cQuote & _
                me!EmpLastName & cQuote

In trouble with the delimitor to use? not anymore, with ADO, just ask:

Dim prefix As String
Dim suffix As String
Dim rst As ADODB.Recordset
Set rst= ........
' now, prepare a comparison with "FieldName":
PrefixAndSuffixForDataType rst.FieldName.Type, prefix, suffix

' we have the right delimitors, use them...
mySql = "... FieldName=" & prefix & Value & suffix & "..."

because ADO knows the delimitor to be use, through the appropriate "schema", it can chat it back to you. Here is the function PrefixAndSuffixForDataType:

'************* Code Start **************
' This code was originally written by Michel Walsh
' It is not to be altered or distributed,
' except as part of an application.
' You are free to use it in any application,
' provided the copyright notice is left unchanged.
'
' Code Courtesy of
' Michel Walsh
'
Public Sub PrefixAndSuffixForDataType(ByVal DataType As Long, _
                                ByRef prefix As String, _
                                ByRef suffix As String, _
                                Optional ByVal d_prefix As String = "", _
                                Optional ByVal d_suffix As String = "")
' Return litteral prefix and suffix to be use with the given data type,
'               for the default data provider.
' ex.: return "#" and "#"  for adDate with Jet 4.0

Dim rst As ADODB.Recordset
    On Error GoTo NotSupported
    Set rst = CurrentProject.Connection.OpenSchema(adSchemaProviderTypes)
    rst.Find "DATA_TYPE=" & DataType
    If Not rst.EOF Then
        prefix = Nz(rst!LITERAL_PREFIX, d_prefix)
        suffix = Nz(rst!LITERAL_SUFFIX, d_suffix)
    Else    ' don't brother to return an error
            ' even if this DataType is probably not supported,
            ' just use the default values supplied by the user
        prefix = d_prefix
        suffix = d_suffix
    End If
    rst.Close
    Set rst = Nothing
    Exit Sub

NotSupported:

    On Error Resume Next
    prefix = d_prefix
    suffix = d_suffix

    ' take no chance, make a careful cleaning (probably excessive, but we
    '   don't care about speed, here.)
    If Not (rst Is Nothing) Then
        If (rst.State And adStateClosed) <> adStateClosed Then rst.Close
        Set rst = Nothing
    End If
End Sub
'   ***** Code End ******

Final note: you still need to use US format for date_time field values.