Query with Multiple Criteria

  • Thread starter Thread starter ant1983
  • Start date Start date
A

ant1983

Hi,

I have a form called frmEmailScheduleToClientMenu and on that form i have
the following unbound combo boxes:

txtClient (Row Source: SELECT [tblClientContact].[autClientID],
[tblClientContact].[txtCompany] FROM tblClientContact ORDER BY [txtCompany];)

txtLevel (Row Source: SELECT [suptblLevel].[autLevelID],
[suptblLevel].[txtLevelAbbreviation] FROM suptblLevel ORDER BY
[txtLevelAbbreviation]; )

txtFacilitator (Row Source: SELECT [tblFacilitator].[autFacilitatorID],
[tblFacilitator].[txtName] FROM tblFacilitator ORDER BY [txtName];

txtCourse (Row Source: SELECT [suptblCourse].[autCourseID],
[suptblCourse].[txtCourse] FROM suptblCourse ORDER BY [txtCourse]; )

Then i have a command button cmdPreview that opens a report
rptTrainingDateForClientsWithCriteria which runs off a query
qryTrainingDateForClientWithCriteria. Both the query and report have the
following fields (and some more):

dateStartDate
dateEndDate
txtCourse
txtClient
txtLevel
txtFacilitator

Basically what i want to do is when i click cmdPreview to open the report i
want the report populated with the criteria i have specified on the above
form.

Now i know how to do this if i have only one criteria. I usually go to the
query and then build the following. Lets take txtLevel for example:

[Forms]![frmEmailScheduleToClientMenu]![txtLevel]

.... but how do i build the query so that i can see different reports by
selecting different criteria?

Thanks!
 
If you want to be able to leave some of those boxes blank (so they're not
filtering the report), the best solution will be to omit the criteria from
your query and use the WhereCondition of OpenForm instead.

Download the example in this article:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html
It shows how to build the filter string. In the end, it applies the filter
to the form, but it's exactly the same process to build the filter for a
report.
 
There are a number of ways to do this....

I would suggest that the easiest would be to dynamically write the SQL for
the query that drives the report....

your code should look something like this....
(assumes not all controls will be populated

dim strSQL

strSQL = "SELECT * FROM tblSource WHERE "

'Control 1
if Control1.value <> Empty then
strSQL = strSQL & "fieldA = '" & control1.value & "' AND "
'Note use of Single quote, Double Quote, Space apersand to open
'and apersand, space, Double Quote, Single Quote to close
end if

'Control 2
if Control2.value <> Empty then
strSQL = strSQL & "fieldA = '" & control2.value & "' AND "
end if

etc

'Remove
If right(strSQL,4) = "AND " then
strsql = left(strSQL,len(strSQL)-4)
elseif right(strSQL,6) = "WHERE " then
strsql = left(strSQL,len(strSQL)-6)
end if

currentdb.Querydefs("QueryName").SQL = strSQL

'Now run report
 
Ant1983 -

I don't think you can do this in a single query because of the criteria NOT
chosen. (If all criteria must be chosen, then you can simply follow your
method for each field with criteria.)

The way I have resolved this is to create the SQL for the query behind the
form, looping through the criteria and adding any to the WHERE clause for
those with criteria chosen. You do this in the code behind the button to
print or print preview the query. You can start by setting up a string
variable to hold the SQL (e.g. strSQL). Then take the query you like and
from the SQL View, copy/paste the query into the code block, assigning it to
the strSQL. You will need to add the quotes and remove everything from the
WHERE clause on. If the SQL statement is large, you will need to adjust for
line breaks, etc. in the code. For example:

strSQL = "SELECT field1, field2, field3 FROM tableOrQueryName "

If there is no criteria entered on the form, you need to add the final
semicolon to the query:

strSQL = strSQL & ";"

If any criteria is entered on the form, you will need to add a WHERE clause
to the query:

strSQL = strSQL & " WHERE "

And then for each criteria, you must add the condition based on the form.
You will need to append the form data outside of any double-quotes, and you
will need to enter any required delimeters for text or date fields. You need
to end each with a comma and space. For example:

strSQL = strSQL & " field1 = '" &
[Forms]![frmEmailScheduleToClientMenu]![txtLevel] & "', "

When you are done adding these for each criteria that has been entered
(don't add anything for fields with no criteria), then you need to remove the
trailing comma and space and add the final semicolon.

strSQL = left(strSQL, len(strSQL) - 2) & ";"

If you had any other clauses that are important (ORDER BY, GROUP BY, etc.)
that normally come after the WHERE clause, then they need to be added before
the semicolon in all cases.

Now that you have the SQL statement built, you can create a query from it,
and then run the query. There are several options on how to do this, but I
would recommend creating a querydef from it (a named one so it will become a
query in your database) so you can look at the query outside of the code.
Look up help on QueryDef in Access. If you use this, then after the first
time you create the querydef, you will want to add code to delete it before
re-creating it the next time the code runs. Once the queryDef is defined,
you can run it (including print preview or print) like any other query.

It sounds complicated, but it is pretty straight-forward. Watch out for
missing spaces and missing delimeters in the strSQL. (You may want to do a
Debug.Pring strSQL with a breakpoint so you can see the exact SQL being built
in the immediate window.)

Good luck!
--
Daryl S


ant1983 said:
Hi,

I have a form called frmEmailScheduleToClientMenu and on that form i have
the following unbound combo boxes:

txtClient (Row Source: SELECT [tblClientContact].[autClientID],
[tblClientContact].[txtCompany] FROM tblClientContact ORDER BY [txtCompany];)

txtLevel (Row Source: SELECT [suptblLevel].[autLevelID],
[suptblLevel].[txtLevelAbbreviation] FROM suptblLevel ORDER BY
[txtLevelAbbreviation]; )

txtFacilitator (Row Source: SELECT [tblFacilitator].[autFacilitatorID],
[tblFacilitator].[txtName] FROM tblFacilitator ORDER BY [txtName];

txtCourse (Row Source: SELECT [suptblCourse].[autCourseID],
[suptblCourse].[txtCourse] FROM suptblCourse ORDER BY [txtCourse]; )

Then i have a command button cmdPreview that opens a report
rptTrainingDateForClientsWithCriteria which runs off a query
qryTrainingDateForClientWithCriteria. Both the query and report have the
following fields (and some more):

dateStartDate
dateEndDate
txtCourse
txtClient
txtLevel
txtFacilitator

Basically what i want to do is when i click cmdPreview to open the report i
want the report populated with the criteria i have specified on the above
form.

Now i know how to do this if i have only one criteria. I usually go to the
query and then build the following. Lets take txtLevel for example:

[Forms]![frmEmailScheduleToClientMenu]![txtLevel]

... but how do i build the query so that i can see different reports by
selecting different criteria?

Thanks!
Click to expand...
 
Back
Top