Hello all,
I have a problem with a search whereby I have multiple fields that can optionally be filled in to search; these are Unbound textboxes and a couple combo boxes. They reference multiple fields. Here's an example form:
This search has six fields; I want to be able to search on any of those fields:
These results are fine when all the fields are filled out, as I believe I can get my search results to give me what I want:
The problem I'm having is when the field is skipped, having those 'spaces' in the search (highlighted).
Here's the VBA I want to use for the search:
Code:
Private Sub btnSearch_Click()
Dim strFilter As String
Dim strSearch As String
On Error Resume Next
If Me.txtSearch.Text <> "" Then
strSearch = "'*" & Replace(Me.txtSearch.Text, "'", "''") & "*'"
strFilter = "[FirstName] Like " & strSearch & " OR [LastName] Like " & strSearch & " OR [SSN] Like " & strSearch _
& " OR [City] Like " & strSearch & " OR [State] Like " & strSearch & " OR [Country] Like " & strSearch
Me.Filter = strFilter
Me.FilterOn = True
Else
Me.Filter = ""
Me.FilterOn = False
End If
If Me.Recordset.RecordCount = 0 Then
Me.Filter = ""
Me.FilterOn = False
Me.txtSearch.SetFocus
Me.txtSearch.Text = ""
MsgBox "No results found."
Exit Sub
End If
End Sub
Does anyone know how to get this search working appropriately? Again, I want the results to work whether or not a field is filled in and to read that non-filled in value as null, rather than a " " identified in the text box for the search field.
Thank you!