Need help sorting out ANDs and ORs in parameter query

Need help sorting out ANDs and ORs in parameter query

Post by Melinda Ch » Sat, 16 Feb 2008 04:59:46

I have a query that's probably far more complicated than it needs to be. It
gets its parameters from a form where people can choose various criteria
they'd like to base their report on. On the form you can choose from a list
box of Project Types, several check boxes of Services Provided, a combo box
of Counties, and a text box with a Year. If I select some items from the
list box or any of the check boxes, my query shows me all of the projects
I'd expect to see. However, if I select a county my report comes up blank.
If I change the year, it doesn't seem to change the results.

What I'd like to see in the results are the projects that are in the County
AND in the Year AND with the selected Job Type OR the selected Service
Provided. So, if someone chooses Bridge Inspections from the list box,
Environmental Document from the check boxes, Stark County from the combo box
and 2003 for the year, I'd see all Bridge Inspections or Environmental
Document projects performed in Stark County in 2003.

Here is my query:
SELECT tblJobInfo.ProjectNumber, tblJobInfo.County, tblJobInfo.YearDesigned,
tblClients.ClientName, tblContact.ContactName, tblContact.ContactPhone,
tblWorkType.JobType, tblServicesProvided.ConstAdmin,
tblServicesProvided.ConstInsp, tblServicesProvided.BidDoc,
FROM (((tblJobInfo LEFT JOIN tblClients ON
tblJobInfo.ClientID=tblClients.ClientID) LEFT JOIN tblServicesProvided ON
tblJobInfo.JobID=tblServicesProvided.JobID) LEFT JOIN tblWorkType ON
tblJobInfo.JobType=tblWorkType.JobTypeID) LEFT JOIN tblContact ON
WHERE (((tblJobInfo.County) Like ("*" & forms!frmMasterSearch!txtCounty &
"*")) And ((tblJobInfo.YearDesigned)>forms!frmMasterSearch!year-"1") And
((tblServicesProvided.ConstAdmin)=forms!frmMasterSearch!chkConstAdmin)) Or
(((tblServicesProvided.ConstInsp)=forms!frmMasterSearch!chkConstInsp)) Or
(((tblServicesProvided.BidDoc)=forms!frmMasterSearch!chkBidDoc)) Or
(((tblServicesProvided.Enviro)=forms!frmMasterSearch!chkEnviro)) Or
ORDER BY tblJobInfo.YearDesigned DESC;

Need help sorting out ANDs and ORs in parameter query

Post by Jeff Boyc » Sat, 16 Feb 2008 09:20:05


One approach to ending up with the SQL statement you're after is to start in
the query design window in Access.

You can set up your query to use the values found in the forms' controls AND
you can control whether you are using AND's and/or ORs. If you have more
than one condition/combination, you just use a second Selection Criterion

When the query is working as expected, return to the design view and click
on the SQL View (upper left toolbar button/dropdown). Access will display
the SQL statement of your (now working) query.

Good Luck!


Jeff Boyce
Microsoft Office/Access MVP

"Melinda Chase" < XXXX@XXXXX.COM > wrote in


Need help sorting out ANDs and ORs in parameter query

Post by Melinda Ch » Sat, 16 Feb 2008 23:02:10

hanks for the suggestion, Jeff.

The query design window is how I'd initially made my query, so I went back
there and did some fiddling. I got it working. Turns out I needed a Nz in
one place.

"Jeff Boyce" < XXXX@XXXXX.COM > wrote in message
news:e4t% XXXX@XXXXX.COM ...