Using the OR criteria. Am I doing it right?

[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));

 

[KenSheridan via AccessMonster.com]

You can simplify it like this:

SELECT [Last Name], [First Name], [Equipment Model],
[Rx Date], [Delivery Date], [Billing Date], [Paid Date],
Comments, ProgressNote, RefFirstName, RefLastName,
[Equipment Model], [Patient Status], ReferralID, DateOfReferral
FROM qrDetail
WHERE ([Patient Status]=Forms![YourForm]![cbpatientdetail]
OR Forms![YourForm]![cbpatientdetail] IS NULL)
AND (ReferralID=Forms![YourForm]![cbreferraldetail]
OR Forms![YourForm]![cbreferraldetail] IS NULL)
AND (Month([DateOfReferral]=Forms![YourForm]![cbMonth]
OR Forms![YourForm]![cbMonth] IS NULL);

changing YourForm to the actual form name of course.

Each parenthesised Boolean OR operation will evaluate to TRUE if the value in
the column matches the value in the relevant control on the form or the
control is Null, i.e. skipped. All of the parenthesised expression must
evaluate to TRUE for a row to be returned by virtue of the Boolean AND
operations.

So if a user selects a value in one control but skips the others then the
expression relating to the shipped controls will evaluate to TRUE for every
row, but only to TRUE in the case of the expression relating to the control
in which a value is selected where the value in the relevant column matches
the selected value. Consequently only those rows will be returned.

If a user selects value on two controls then, on the same basis only those
rows where the values in the two columns match the selected values will be
returned.

The effect is to make each parameter optional; a user can select as few or as
many as they wish in combination.

BTW I'd strongly recommend that you save the query in SQL view and not in
design view as otherwise when you come to open it again in design view Access
will have moved things around. It will work just the same, but you might
find it difficult to make any amendments such as adding another optional
parameter should that be necessary. In SQL vie wits just a question of
adding another:

AND ([SomeField]=Forms![YourForm]![cbSomeCombo]
OR Forms![YourForm]![cbSomeCombo] IS NULL)

Ken Sheridan
Stafford, England

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));

 

[KenSheridan via AccessMonster.com]

Oops, missed a closing parenthesis:

SELECT [Last Name], [First Name], [Equipment Model],
[Rx Date], [Delivery Date], [Billing Date], [Paid Date],
Comments, ProgressNote, RefFirstName, RefLastName,
[Equipment Model], [Patient Status], ReferralID, DateOfReferral
FROM qrDetail
WHERE ([Patient Status]=Forms![YourForm]![cbpatientdetail]
OR Forms![YourForm]![cbpatientdetail] IS NULL)
AND (ReferralID=Forms![YourForm]![cbreferraldetail]
OR Forms![YourForm]![cbreferraldetail] IS NULL)
AND (Month([DateOfReferral])=Forms![YourForm]![cbMonth]
OR Forms![YourForm]![cbMonth] IS NULL);



Ken Sheridan
Stafford, England

 

[Mark]

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