Creating a form to search for records based on multiple criteria

Question

I am trying to create a form that allows you to return results based on multiple criteria.

I have FirstName field, LastName field, and State Field.

I also have an text boxes named searchFirst, searchLast, searchState where users can input criteria.

The search button will execute the following code once clicked.

Private Sub mySearchQuery_Click()

Dim filter As String

Dim rtFirstName As String
Dim rtLastName As String
Dim rtState As String

rtFirstName = Me.searchFirst.Value
rtLastName = Me.searchLast.Value
rtState = Me.searchState.Value

If Not IsNull(rtFirstName) Then
        If Not IsNull(filter) Then filter = filter & " AND "
        filter = filter & "(FirstName like""*" & rtFirstName & "*"")"
End If


If Not IsNull(rtLastName) Then
        If Not IsNull(filter) Then filter = filter & " AND "
        filter = filter & "(LastName like""*" & rtLastName & "*"")"
End If

If Not IsNull(rtState) Then
        If Not IsNull(filter) Then filter = filter & " AND "
        filter = filter & "(State LIKE""*" & rtState & "*"")"
End If

' Now re-construct the SQL query '
Dim sql As String
sql = "SELECT * FROM MainData"

If Not IsNull(filter) Then
        sql = sql & " WHERE " & filter
End If

Me.RecordSource = sql
'SubForm.Form.RecordSource = sql

End Sub

I am getting the following error below.

Run-time error '3075': Syntax error (missing operator) in query expression 'AND (FirstName like"*tracy*") AND (lastName like"*Smith*") AND (State LIKE"*ga*")'.

I am not sure why AND was included at the beginning of the search query?

HansUp HansUp · Accepted Answer · 2016-02-16T18:52:01

I am not sure why AND was included at the beginning of the search query?

Since you have Dim filter As String, filter can never contain Null. That means these If conditions ... If Not IsNull(filter) ... will always be True.

Similarly, Not IsNull(rtFirstName), Not IsNull(rtLastName), and Not IsNull(rtState) will always be True.

The net result is the code adds another condition piece to your filter string regardless of whether or not the corresponding search text box contains anything, and each of those pieces is prefixed with " AND ".

With those points in mind, you could refactor your code to add a filter segment only when you have something in the corresponding search text box and decide when to include " AND ". However I find it simpler to include " AND " for each of them and then strip away the very first " AND " from filter before adding it to the WHERE clause.

Private Sub mySearchQuery_Click()
    Dim strSelect As String
    Dim strWhere As String

    If Len(Trim(Me!searchFirst.Value) & vbNullString) > 0 Then
        strWhere = strWhere & " AND FirstName Like ""*" & Me!searchFirst.Value & "*"""
    End If

    If Len(Trim(Me!searchLast.Value) & vbNullString) > 0 Then
        strWhere = strWhere & " AND LastName Like ""*" & Me!searchLast.Value & "*"""
    End If

    If Len(Trim(Me!searchState.Value) & vbNullString) > 0 Then
        strWhere = strWhere & " AND State Like ""*" & Me!searchState.Value & "*"""
    End If

    ' Now re-construct the SQL query
    strSelect = "SELECT * FROM MainData"

    ' only add WHERE clause if we have something in strWhere    
    If Len(strWhere) > 0 Then
        ' use Mid() to ignore leading " AND "
        strSelect = strSelect & " WHERE " & Mid(strWhere, 6)
    End If
    Debug.Print strSelect ' <- inspect this in Immediate window; Ctrl+g will take you there

    ' enable one of these RecordSource lines after confirming Debug.Print shows you what you need
    'Me.RecordSource = sql
    'SubForm.Form.RecordSource = sql
End Sub

Creating a form to search for records based on multiple criteria

1 Answers