creating dynamic search form/sql query... (BuildCriteria)

I'm creating a Search form which has the various fields from the database/tables in it.  I'm using the same code/example provided in help.

However, the code only explains how to cycle through Textbox controls.  I have some checkbox controls (and Yes/No options in the tables) that I would like to search for as well.  (i.e. return records where the checkbox is enabled.)

Here's the code I'm using, and the checkbox attempt I've made... but it's not working.  Crashed on the first line of the BuildCriteria function.. :(

   Dim ctl As Control
    Dim sSQL As String
    Dim sWhereClause As String
    Dim sGroupClause As String
   
    'Initialize the Where Clause variable.
    sWhereClause = " Where "
    sGroupClause = " GROUP BY tblMain.[Client Name]...."
   
    'Start the first part of the select statement.
    sSQL = "SELECT tblMain.ID.... FROM ... "
 
'Loop through each control on the form to get its value.
    For Each ctl In Me.Controls
        With ctl
            'The only Control you are using is the text box.
            'However, you can add as many types of controls as you want.
            Select Case .ControlType
                Case acTextBox
                    .SetFocus
                    'This is the function that actually builds
                    'the clause.
                    If sWhereClause = " Where " Then
                        sWhereClause = sWhereClause & BuildExpression(.Name, dbText, .Text)
                    Else
                        sWhereClause = sWhereClause & " and " & BuildExpression(.Name, dbText, .Text)
                    End If
                Case acComboBox
                    .SetFocus
                    'This is the function that actually builds
                    'the clause.
                    If sWhereClause = " Where " Then
                        sWhereClause = sWhereClause & BuildExpression(.Name, dbByte, .Value)
                    Else
                        sWhereClause = sWhereClause & " and " & BuildExpression(.Name, dbByte, .Value)
                    End If
                   
                Case acCheckBox
                    .SetFocus
                    'This is the function that actually builds
                    'the clause.
                    If .Value = -1 Then
                        If sWhereClause = " Where " Then
                            sWhereClause = sWhereClause & BuildCriteria(.Name, dbByte, .Text)
                            MsgBox "!"
                        Else
                            sWhereClause = sWhereClause & " and " & BuildCriteria(.Name, dbByte, .Text)
                            MsgBox "!!"
                        End If
                    Else
                    End If
            End Select
        End With
    Next ctl
Public Function BuildExpression(pstrField As String, _
                                ptypFieldType As DataTypeEnum, _
                                pstrExpression As String) As String
 
    BuildExpression = BuildCriteria(pstrField, ptypFieldType, pstrExpression)
    If ptypFieldType = dbText Then
        BuildExpression = Replace(BuildExpression, "=", " Like ")
        BuildExpression = Replace(BuildExpression, pstrExpression, "*" & pstrExpression & "*")
    End If
End Function

Start Free Trial