multiple optional criteria

[TheShabz]

Hi all,



I'm working on a reporting system that runs some analysis on the effectiveness of our campaigns. I have a table that has all of the enrollments for the year. A form will be used to create a report of some totals and percentages determining the effectiveness of a campaign. The user will be able to select a specific campaign, a zip code, the contractor the campaign was assigned to, or any combination of the three to group the enrollments by for analysis. This leads to 9 possible combinations and 9 possible queries. Currently, my plan is the create a select case and write out the SQL for each combination. This seems a bit brute force to me.

Is there a more efficient way to attack this?

[ajetrumpet]

that's the only other way sir, unless you want a query that looks like this:

SELECT * FROM table WHERE

((field1 = forms!form!field1 OR forms!form!field1 IS NULL) AND 
((field2 = forms!form!field1 OR forms!form!field2 IS NULL) AND 
((field3 = forms!form!field1 OR forms!form!field3 IS NULL) AND 
((field4 = forms!form!field1 OR forms!form!field4 IS NULL) AND 
((field5 = forms!form!field1 OR forms!form!field5 IS NULL) AND 
((field6 = forms!form!field1 OR forms!form!field6 IS NULL) AND 
((field7 = forms!form!field1 OR forms!form!field7 IS NULL) AND 
((field8 = forms!form!field1 OR forms!form!field8 IS NULL) AND 
((field9 = forms!form!field1 OR forms!form!field9 IS NULL))

that's the only way you can effectively deal with optional criteria in forms.

[pbaldy]

I often use Adam's technique when there's only a couple of fields, but the query design grid will often take that and make it unreadable. You might try it with your 3, which may still be manageable. When I have more than a couple, I tend to create dynamic SQL, as is done in the sample db here:

http://www.baldyweb.com/BuildSQL.htm

In your situation, rather than setting a recordsource I'd build a wherecondition to use with this technique:

http://www.baldyweb.com/wherecondition.htm

[ajetrumpet]

the query design grid will often take that and make it unreadable.

AMEN. Can we participate in MS bashing now?

[pbaldy]

Sure; I've got all kind of pet peeves with MS.

[TheShabz]

This thread is now about your issues with Access "features"

[cheshire_smile]

ajetrumpet and pbaldy have already hinted at this but:

Generate a query in the record source of the report, select each field that belongs in the report, and set the criteria to the the Form's controls.

The Form needs to stay open when you generate the report.

[ajetrumpet]

This thread is now about your issues with Access "features"

Who's? MINE? what feature? the jumbled SQL feature? I believe the version of that is now at ?.?.?.?