Access Parameter Query - Multiple Values Criteria 1

[corycrum]

Hi,

I have a form that provides inputs to a parameter query. I'm using a combo-box to filter the query results by department (Human Resources, Legal, etc). Currently, the combo box lists each department, but I would like the combo box to also provide an "All Departments" option in the drop-down list. If "All Departments" is selected, the query should not filter any of the results. We have approximately 20 departments.

What is the best way to construct the criteria in the query so that the user can select either a single department (i.e. Human Resources) or "All Departments"?

Thanks so much for your help.

Cory

 

[PHV]

What is the actual SQL code of your query ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[corycrum]

I don't know if you need all of the SQL as it is quite lengthy. I believe this is the segment you will need.

WHERE (((TBL_COST_CENTERS.DEPARTMENT)=[Forms]![FRM_QUERY_BY_DEPARTMENT]![DEPARTMENT])

I tried using an IIF statement in the criteria that read:

=IIF( Forms![FRM_QUERY_BY_DEPARTMENT]![DEPARTMENT]="All Departments","", Forms![FRM_QUERY_BY_DEPARTMENT]![DEPARTMENT])

Obviously this didn't work.

Thanks so much.

Cory

 

[PHV]

WHERE (TBL_COST_CENTERS.DEPARTMENT=[Forms]![FRM_QUERY_BY_DEPARTMENT]![DEPARTMENT] OR [Forms]![FRM_QUERY_BY_DEPARTMENT]![DEPARTMENT]='All Departments')

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[corycrum]

Amazing. That worked. I must say, I don't understand why. Would you mind explaining some of the intuition behind this?

Thanks so much!

Cory

 

[traingamer]

It's very straightforward:

WHERE ([COLOR=blue]TBL_COST_CENTERS.DEPARTMENT=[Forms]![FRM_QUERY_BY_DEPARTMENT]![DEPARTMENT][/color] OR [COLOR=red][Forms]![FRM_QUERY_BY_DEPARTMENT]![DEPARTMENT]='All Departments'[/color])

Where (the department = the department selected OR I picked 'All Departments'

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill