Multiple Criteria Options

[rwpetrie]

How do I set the criteria of a query to be ("A" and "B") OR ("C" and "D") where the letters correspond to completely separate fields?

Do I have to do that in a SQL / VB statement, and if so, how do I write it?

Thanks all!

 

[thouston]

You don't have to resort to SQL/VB - you can do it in the query design window. Put "A" and "B" in the criteria row in their respective fields on the same line (AND criterion), and the "C" and "D" both on a different line (OR criterion).

This may give you some unexpected results (though it's what you've asked for) - you will get all lines containing A and B (regardless of C and D) and vice versa. You can experiment with repeating different combinations of criteria on different lines to get what you want. Good luck!

 

[rwpetrie]

thanks, but....

I appreciate the help, but I probably didn't explain well enough. The A, B, C, and D are ALL different fields. I know what you are saying, but it isn't that I want two options in the same field, OR two options in another field. There are four fields, and I need either (a criteria in one and a criteria in a second), or (a criteria in a third and a criteria in a fourth)

See what I mean?

 

[Jon K]

Assuming A B C D are all text fields and you want to have
(A="xxx" and B="yyy") or (C="zzz" and D="xyz"),

switch to SQL View of the query and add the above as a Where clause after the From clause:-

SELECT ....
FROM ....
WHERE (A="xxx" and B="yyy") or (C="zzz" and D="xyz")
ORDER BY ...


(You can then switch back to Design View to see how Access has set the criteria in the query grid.)

 

[thouston]

The design view grid would look like this:

Field:____|FieldA____|FieldB____|FieldC____|FieldD
Table:___|XXX______|XXX_____|XXX_____|XXX_____|
Sort:_____|_________|_________|________|_________|
Show:__|_________|_________|________|_________|
-----------------------------------------------------------
Criteria:_|"A"_______|"B"_______|_________|________|
Or:_____|_________|__________|"C"______|"D"______|

Hope this helps