Query multiple fields for the same criteria

[labratKY]

Greetings,



I'm using Access 2010 and am trying to find records whose fields match a list. The below where statement works. My problem is there can be up to 19 TESTs within each record (TEST0, TEST1, TEST2,..TEST19) any one of which may match something in my list. It's possible only TEST19 may match and I need the search to continue until all TESTn's present are looked at.

WHERE (((SCCODBC_ORDERS.COLLECT_DATE)>#8/3/2014#) AND ((SCCODBC_ORDERS.TEST0) In ("LUAN2","MPSS","PRELE")));

Could anyone point me in the right direction?

TIA!
John

[pbaldy]

Well, the right direction would be to correct the design of the database (normalize it). If you're stuck with it (note the important parentheses):

WHERE SCCODBC_ORDERS.COLLECT_DATE>#8/3/2014# AND (SCCODBC_ORDERS.TEST0 In ("LUAN2","MPSS","PRELE") OR SCCODBC_ORDERS.TEST1 In ("LUAN2","MPSS","PRELE") OR ...)

[labratKY]

Thanks Paul! That worked. I was in the ball park, my syntax was off just a bit. This is a clinical lab system so I'm afraid the database is pretty much etched in stone by the LIS vendor. Thanks again.

[pbaldy]

Happy to help, and welcome to the site by the way!