Multiple clauses in quesry

[Mo]

Is there a way to write this query in a more effeciently? Can I combine
the '(SY=2006 or (SY) Is Null)' clause into a single clause instead of
having it in several places as I have done?

Thanks for any help you can give.

Mo


SELECT SY, KASE, REFNO, LAB, CSNM, TERM, TD, TM,
TY, SD, SM, MSN

FROM tblscreening

WHERE (SY=2006 or (SY) Is Null)
AND (TERM) Is Null AND (TD) Is Null AND (TM) Is Null AND (TY) Is Null

OR (SY=2006 or (SY) Is Null)
AND (MAGE) Is Null AND (MD) Is Null AND (MM) Is Null AND (MY) Is Null

OR (SY=2006 or (SY) Is Null)
AND (SD) Is Null AND (SM) Is Null

OR (SY=2006 or (SY) Is Null)
AND (GSTO) Is Null

OR (SY=2006 or (SY) Is Null)
AND (POST) Is Null

OR (SY=2006 or (SY) Is Null)
AND (MATNHS) Is Null

 

[Allen Browne]

The expression:
(SY=2006 OR SY Is Null)
is needed only once.

However, your criteria mixes ANDs and ORs, without clearly bracketing them.
The results may not be what you expect. It's important to understand that:
a AND (b OR c)
is not the same thing as:
(a AND b) OR c
where a, b, and c are expressions.

 

[John Spencer]

It could be written as follows. Although Access will restructure the query if
you save it in design view and you will be back to the SQL you posted.

SELECT SY, KASE, REFNO, LAB, CSNM, TERM, TD, TM,
TY, SD, SM, MSN
FROM tblscreening
WHERE (SY=2006 or SY Is Null)

AND
(
(TERM Is Null AND TD Is Null AND TM Is Null AND TY Is Null)
OR (MAGE Is Null AND MD Is Null AND MM Is Null AND MY Is Null)
OR (SD Is Null AND SM Is Null)
OR (GSTO Is Null)
OR (POST Is Null)
OR (MATNHS Is Null)
)

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Mo]

Thanks to both of you for the advice.

@ John. I'm using the sql as the recordsource of a form, so
hopefully it'll remain pretty stable!

Mo