jaitken0308
Registered User.
- Local time
- Yesterday, 23:42
- Joined
- Jan 23, 2014
- Messages
- 30
ANSWER: The code you seek (if you had the same question I did, anyway) can be found in post #11.
Not sure if this is the right place for this question, so please forgive me if it is. It does involve VBA, but also involves forms and searches. Here goes...
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.
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.
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? Thanks in advance for any help.
Not sure if this is the right place for this question, so please forgive me if it is. It does involve VBA, but also involves forms and searches. Here goes...
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.
Code:
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.RequeryI 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.
Code:
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.RequeryLike 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? Thanks in advance for any help.
Last edited:
If they were you would be changing them every time someone changed the criteria.
