Unable to get search criteria to work

thandel
thandel used Ask the Experts™
on
Have been using the following code with perfect results:

GCriteria = "OptA" & " LIKE '*" & FindRecText & "*'" & " OR OptB" & " LIKE '*" & FindRecText & "*'"
Form_FEntryQuery.RecordSource = "select * from tObj where " & GCriteria & ""

But now I would like to add "OptC" along with OptA for either to be found.  Ex:

GCriteria = "OptA" & " Or " & "OptAC" & " LIKE '*" & FindRecText & "*'" & " OR
OptAB" & " LIKE '*" & FindRecText & "*'"

My results are not finding the correct records... what am I doing wrong with my syntax?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
Distinguished Expert 2017
Commented:
Each criteria MUST be expressed separately.

GCriteria = "OptA LIKE '*" & FindRecText & "*' OR OptB LIKE '*" & FindRecText & "*' OR OptC LIKE '*" & FindRecText & "*'"

I also cleaned the criteria up a little.

Just FYI, the options are not properly normalized and belong in a separate table.  Once you have more than one of something, you have many.  Then the criteria would be:

GCriteria = "t.Opt.Option LIKE '*" & FindRecText & "*'"
Form_FEntryQuery.RecordSource = "select tObj.*, tOpt.* from tObj Inner Join tOpt  where " & GCriteria & ""

Creating repeating groups (multiple fields holding the same type of data such as options, childNames, favorites, months, etc) is a common error made by people new to using a RDBMS.
thandel

Author

Commented:
Thanks Pat!
PatHartman
Distinguished Expert 2017

Commented:
You're welcome :)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial