Sending Multiple Criteria to Report

[mikeyl62]

ok, I have a report with about 30 fields that produce a "Production
Bag" for me.
I want to be able to pass multiple criteria to the report depending on
what I put in,
For example:

Bag#: 456
Job#: 231
Date: 2/3/06
Order#: 54

so this query should print out a Production Bag meeting all this
criteria
If I decide to only enter Order#: 54 and leave the rest blank, the
report should show me all jobs within Order# 54

Right now I enter this in the criteria of the report
under
Bag#
[Forms]![JobBagSelection]![Bag#] or ([Forms]![JobBagSelection]![Bag#]
is Null)

Job#
[Forms]![JobBagSelection]![Job#] or ([Forms]![JobBagSelection]![Job#]
is Null)

the problem is after I enter about 4 or 5 of these into the criteria,
SQL says the statement is to complex

how do I pass more then 4 parameters to a report?
I hope i'm clear on this

 

[Jeff Boyce]

One approach to handling this is to use a form to "order" the report. Use a
textbox (or combobox) control for each of the potential selection criteria.

Behind the command button you push to open the report, add in a routine that
dynamically builds a SQL statement that will be the "where clause" for your
report.

In the example you gave, the where clause would only include "Where [Order#]
= 54", since the other controls would be empty/no value selected.

Regards

Jeff Boyce
Microsoft Office/Access MVP