Using the OR criteria. Am I doing it right?

  • Thread starter Thread starter Mark
  • Start date Start date
M

Mark

Hi all,

I have a query that runs based on many filters (combo Selection box) that a
user is able to select from a form. I would like the results to be based on
any filter they use and if the filter is not use then skip that as criteria.
To do this I used the OR statement in the criteria and proceeded to try and
find every possible combination. Looking at the query below did I do it
right? Can this be simplified anyhow? In the criteria you are limited to 9
Or statements and I used them all already, can I add more?


SELECT qrDetail.[Last Name], qrDetail.[First Name], qrDetail.[Equipment
Model], qrDetail.[Rx Date], qrDetail.[Delivery Date], qrDetail.[Billing
Date], qrDetail.[Paid Date], qrDetail.Comments, qrDetail.ProgressNote,
qrDetail.RefFirstName, qrDetail.RefLastName, qrDetail.[Equipment Model],
qrDetail.[Patient Status], qrDetail.ReferralID, qrDetail.DateOfReferral
FROM qrDetail
WHERE (((qrDetail.[Patient Status])=[cbpatientdetail]) AND
((qrDetail.ReferralID)=[cbreferraldetail]) AND
((Month([DateOfReferral]))=[cbMonth])) OR (((qrDetail.[Patient
Status])=[cbpatientdetail]) AND (([cbMonth]) Is Null) AND
(([cbreferraldetail]) Is Null)) OR (((qrDetail.[Patient
Status])=[cbpatientdetail]) AND ((Month([DateOfReferral]))=[cbMonth]) AND
(([cbreferraldetail]) Is Null)) OR (((qrDetail.[Patient
Status])=[cbpatientdetail]) AND ((qrDetail.ReferralID)=[cbreferraldetail])
AND (([cbMonth]) Is Null)) OR (((qrDetail.ReferralID)=[cbreferraldetail]) AND
((Month([DateOfReferral]))=[cbMonth]) AND (([cbpatientdetail]) Is Null)) OR
(((qrDetail.ReferralID)=[cbreferraldetail]) AND (([cbMonth]) Is Null) AND
(([cbpatientdetail]) Is Null)) OR (((qrDetail.[Patient
Status])=[cbpatientdetail]) AND ((qrDetail.ReferralID)=[cbreferraldetail])
AND (([cbMonth]) Is Null)) OR (((Month([DateOfReferral]))=[cbMonth]) AND
(([cbreferraldetail]) Is Null) AND (([cbpatientdetail]) Is Null)) OR
(((qrDetail.[Patient Status])=[cbpatientdetail]) AND
((Month([DateOfReferral]))=[cbMonth]) AND (([cbreferraldetail]) Is Null));
 
Ken,
Thanks for taking the time to help in my understanding of the use of “OR†in
the where clause. You’re right, although I didn’t doubt you, saving in the
design mode changes it up drastically.
Again thanks,
Mark
 
Back
Top