Form with multi-criteria searches - uses strings and filters

Question

I have a search form with blank fields tied to a table, four criteria search boxes, and a button to take the input from the search boxes, search the table, and populate the results on the form's blank fields.

As of now, it works as long as all four criteria boxes aren't null.
I used filters to achieve this, and here's the code that works as long as all four boxes are not empty. (My criteria boxes are as follows: a textbox called "Keyword" and three combo boxes called HRCombo, BuildingCombo, and RoomCombo, and the fields they're tied to are as follows: "Item Description" "HR Holder" "Building" "Room") My first line "Me.Filter = ..." was broken up to make it easier to view.

Me.Filter = "[Item Description] Like " & Chr(34) & Me.Keyword & "*" & Chr(34) & "
 AND [HR Holder] = '" & Me.HRCombo & "'" & " AND [Building] = '" & Me.BuildingCombo
 & "'" & " AND [Room] = '" & Me.RoomCombo & "'"
Me.FilterOn = True
Me.Requery

I need it to be able to do the search no matter which combination of criteria boxes have input. Someone recommended using if statements to do the following: Create four strings, one for each criteria box. Use 4 if statements to check if the box is null - if it is null, assign an asterisk to its string, and if its not null, assign the value I used for the above Me.Filter statement to each box's string. Then, use Me.Filter and concatenate the four strings at the end.

Here's the code I used for this, and, with my limited knowledge, I can't get it to work.

Dim StrA as String, StrB as String, StrC as String, StrD as String

If Me.Keyword is null then
StrA = "*"
else
StrA = [Item Description] Like " & Chr(34) & Me.Keyword & "*" & Chr(34)
End If

If Me.HRCombo is null then
StrB = "*"
else
StrB = [HR Holder] = '" & Me.HRCombo & "'"
End If

If Me.BuildingCombo is null then
StrC = "*"
else
StrC = [Building] = '" & Me.BuildingCombo & "'"
End If

If Me.RoomCombo is null then
StrD = "*"
else
StrD = [Room] = '" & Me.RoomCombo & "'"
End If

Me.Filter = "StrA & " AND "StrB & " AND "StrC &" AND "StrD"
Me.FilterOn = True
Me.Requery

Like I said, I have a limited knowledge, so I'm sure there's probably missing quotes and commas, or too many of them. Any ideas?

HK1 HK1 · Accepted Answer · 2014-01-23T19:47:35

You're missing some important quotes and your logic to check for null is correct for SQL but not correct for VBA. I'm posting here what I believe is a cleaner way of doing this. Just be aware that you're not escaping single quotes that might be entered in your controls and neither am I in this code (except in the first one, just so you can see how to do it using the Replace function). That's a pretty important thing to do any time a single quote could possibly land up in one of the search/filtering controls.

Dim strWhere as String

If Nz(Me.Keyword, "") <> "" Then
    strWhere = strWhere & "[Item Description] Like '*" & Replace(Me.Keyword, "'", "''") & "*' AND "
End If

If Nz(Me.HRCombo, "") <> "" Then
    strWhere = strWhere & "[HR Holder] = '" & Me.HRCombo & "' AND "
End If

If Nz(Me.BuildingCombo, "") <> "" Then
    strWhere = strWhere & "[Building] = '" & Me.BuildingCombo & "' AND "
End If

If Nz(Me.RoomCombo, "") <> "" Then
    strWhere = strWhere & "[Room] = '" & Me.RoomCombo & "' AND "
End If

If strWhere <> "" Then
    strWhere = Left(strWhere, Len(strWhere)-5) 'Remove the extra AND
    Me.Filter = strWhere
    Me.FilterOn = True
Else
    Me.Filter = ""
    Me.FilterOn = False
End If

Form with multi-criteria searches - uses strings and filters

1 Answers