weird query situation, need help!

[Jerome]

Hi,

In a legacy database there is a text field named 'codes' and the records
look like this:

RecordID; codes;
1; A,B,Q1,Z2,Z4;
2; B,D,E,Q,T;
etc

Now I need to be able to select the records according to INCLUDE and
EXCLUDE options. For example: Show all the records containing B and Q
but not X and not Z in the 'codes' field.

Ideally the user can enter as much + and - options as he wants, but I
could live with a fixed number of options (ie. 6 on both sides)

What is the easiest way to do it?

I tried it with multiple text fields on a form and then build very long
criterias in the query design mode, but that doesn't really work. And
the query gets too long anyway ... at least with the method I'm using
right now.

I guess the Instr() would come in handy too. But since I need more then
ONE letter as criteria, I don't know how to do it?

Any help is greatly appreciated!

Jerome

 

[JohnFol]

Instr is probably the best way to go. To use more than one, simply use an
AND between them (if doing a >0), or * if simply returning the character
position.