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,
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
ORDER BY tblJobInfo.YearDesigned DESC;