Forms allow us to search based on multiple conditions
Answers
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