Search form with "AND" and "OR" combined

ericv

Registered User.
Local time
Tomorrow, 05:17
Joined
Jul 28, 2017
Messages
10
Hi all,

I have a continuous form with some controls on the form header to navigate the records.

The first is a search textbox which can look for 'like' keywords in any of 3 of the fields. This works perfectly:

Me.Filter = "[FunctionalLoc] Like '*" & Me.Text79 & "*' Or [Description] Like '*" & Me.Text79 & "*' OR [ConstTypedesc] like '*" & Me.Text79 & "*'"

However, what I'd like is another text box (or 2) that searches through the same fields but combined with "AND" with the one above but I've no idea how to put the syntax together :-(

Please help?
 
What have you tried? You'd use the same method you have so far, but when mixing you should use parentheses to clarify the desired logic. These aren't the same:

(A And B) Or C
A And (B Or C)
 
but combined with "AND" with the one above

That doesn't make complete sense. Look at what pbaldy wrote. Let's call your code with the ORs FilterA and this new code with the ORs replaced with ANDs FilterB. How are you going to combine FilterA with FilterB--with an AND or and OR?

FilterA OR FilterB --- that's equivalent to FilterA because if any of those ORs are true the whole thing is true
FilterA AND FilterB --- that's equivalent to FilterB because if FilterB is false the whole thing is false.

So, there's no point in combining your 2 filters.

Also, you don't need 3 different comparisons. Since the right side of the comparison is always the same (Me.Text79), you concatenate all the left sides together and then do the comparison:

"([Field1] & [Field2] & [Field3]) LIKE '*" & Me.Text79 & "*'"

Also, change the name of Text79 to something that makes sense to you (e.g. 'SearchText').
 
Awesome thanks so much!

Got it going with purely joining the field names with "&" and "AND" all of them with each searchbox. Can filter any of 5 fields with 5 different keywords...pretty powerful.

My syntax is a bit long winded though, how do I combine the "AND" so I don't have to copy it everytime? Currently looks like this:

Me.Filter =
"
([FunctionalLoc] & [Description] & [ConstTypedesc] & [EquipType] & [MainWorkCtr]) Like '*" & Me.Text79 & "*'

and

([FunctionalLoc] & [Description] & [ConstTypedesc] & [EquipType] & [ MainWorkCtr]) Like '*" & Me.Text284 & "*'

and

([FunctionalLoc] & [Description] & [ConstTypedesc] & [EquipType] & [ MainWorkCtr]) Like '*" & Me.Text286 & "*'

and

([FunctionalLoc] & [Description] & [ConstTypedesc] & [EquipType] & [ MainWorkCtr]) Like '*" & Me.Text287 & "*'

and

([FunctionalLoc] & [Description] & [ConstTypedesc] & [EquipType] & [ MainWorkCtr]) Like '*" & Me.Text288 & "*'

"
 
My syntax is a bit long winded though, how do I combine the "AND" so I don't have to copy it everytime?

You can't, that's as succint as its going to get--actually some regex expert might be able to get you there, but I am not him.

Also, is that really the criteria you want? AND? To evaluate to true each one of those must be true. Your 5 fields must contain values from all 5 of those inputs, if for example Text79 is not found but the other 4 are, that record will not be returned in your result.
 
plog...you made me rethink...I just had to create one searchbox for each field separately and "AND" them together...(now I feel silly)

Cheers champ :-)
 

Users who are viewing this thread

Back
Top Bottom