Using Multiple Criteria from a form

[bluedemon186]

Hello,

I have a query that I am using to generate a report. The criteria for this query is pulled from Combo boxes on a form. I have 5 combo boxes on the form and I am using the following as my criteria;

Client: [forms]![frm_report]![Combo115]
Location: [forms]![frm_report]![Combo125]
Product Line: [forms]![frm_report]![Combo133]
Year: [forms]![frm_report]![Combo141]
Month: [forms]![frm_report]![Combo149]

When I run the report by only selecting value from Client or Month, the report works just fine. When I try any other fields, I either get an error "This expression is typed incorrectly, or it is too complex to be evaluated" or it does not return any values in the report.

How would I go about making this work and how can I make it so that the query validates more than 1 criteria and gives results accordingly.

Thank you!

 

[spikepl]

Whatever it is you are doing - do it one step at a time. If you were able to make Month work, then replace Month by one of the other criteria and make that work. One at a time.

Once all work by one by one, add one more and make two work together. And so on.

BTW: Keeping the automated naming of controls like Combo627 is tempting. In 3 months, when you have to expand and change your application, you'll curse yourself for not naming the controls according to what they in fact represent. Check this for naming convention: http://www.access-programmers.co.uk/forums/showthread.php?t=225837

 

[bluedemon186]

I tried doing it 1 by 1 already, but as soon as I add the second 1 in, it stops working.

I used Combo115 as the name as these 5 fields are all there will be for this database. However, I will still go ahead and change it as per your suggestion.

 

[spikepl]

This is how to debug: http://www.baldyweb.com/immediatewindow.htm

 

[bluedemon186]

I am not using VBA for this query. Just a simple query.

 

[spikepl]

We only see what you give us, and that is not much as yet. Supply the query with 2 criteria set. Select the SQL view in the query designer, copyy the text and paste it here.

 

[bluedemon186]

Sorry, I am not an advanced user of Access so wasn't sure what you would need.

I hope this is what you were requesting.

SELECT Import.Client, Import.Year, Import.Location, Import.[Product Line], Import.Month, Import.Revenue, Import.[Total Jobs], Import.[Shuttle/Express Jobs], Import.[Average TAT]
FROM Import
WHERE (((Import.Client)=[forms]![frm_report]![Combo115])) OR (((Import.Year)=[forms]![frm_report]![Combo125])) OR (((Import.Location)=[forms]![frm_report]![Combo133])) OR (((Import.[Product Line])=[forms]![frm_report]![Combo141])) OR (((Import.Month)=[forms]![frm_report]![Combo149]))
ORDER BY Import.Client, Import.Year, Import.Location, Import.[Product Line], Import.Month, Import.Revenue, Import.[Total Jobs], Import.[Shuttle/Express Jobs], Import.[Average TAT];

 

[spikepl]

You have Year, Month as field names as well as blanks and other characters : read the naming convention link. YEar , Month are reserved words you should use something else: http://allenbrowne.com/AppIssueBadWord.html

Fix YeaR & Month