Need help sorting out ANDs and ORs in parameter query

[Melinda Chase]

Hello,
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,
tblServicesProvided.Enviro
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
tblJobInfo.ClientContactID=tblContact.ContactID
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
((IsSelectedVar("frmMasterSearch","lboJobType",[JobTypeID]))=-1)
ORDER BY tblJobInfo.YearDesigned DESC;

 

[Jeff Boyce]

Melinda

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
row.

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!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hello,
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, tblServicesProvided.Enviro
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
tblJobInfo.ClientContactID=tblContact.ContactID
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
((IsSelectedVar("frmMasterSearch","lboJobType",[JobTypeID]))=-1)
ORDER BY tblJobInfo.YearDesigned DESC;

 

[Melinda Chase]

Thanks 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.

Melinda

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 row.

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!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hello,
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, tblServicesProvided.Enviro
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
tblJobInfo.ClientContactID=tblContact.ContactID
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
((IsSelectedVar("frmMasterSearch","lboJobType",[JobTypeID]))=-1)
ORDER BY tblJobInfo.YearDesigned DESC;