filter query result based on combobox

[foxtet]

Hi every one.
In the small db attached I have query1. I want combo box selection in the form to be filtered in the query.
combo box values include.

Morning;Evening;All. When selected All query should append all results.

Fox

 

[Ranman256]

A table will hold the values for the 3 records, but 2 fields.
Caption, qry
Morning,qaMorn
Eve, qaEve
All,qaAll.

The combo will show col 1, for the user.
But the combo value is set to col 2.
When the user picks the item, run the query.

Docmd.openQuery combo1

It will run the append query.

 

[Uncle Gizmo]

I think you could get <All> with a "Union Select"

See more Here:-
https://sites.google.com/site/msacc...ng-combo-boxes/Video-Combo-Filter-All-or-Some

 

[sneuberg]

Since you have a value list in your combo you don't need to have a union query to get All into the combo box row source. You already put it in. All you need is to use the IIF function it the queries criteria like:

IIf([Forms]![frmParam]![Combo0]="All",[Shift],[Forms]![frmParam]![Combo0])

with this function if "All" is selected in the combo box then the where clause is effectively WHERE [Shift] = [Shift] which is true for any value of Shift that is not null. Otherwise the IIF causes the WHERE clause to be effectively WHERE [Shift] = [Forms]![frmParam]![Combo0] so whatever is selected in the combo box determines the result.

If [Shift] can be Null and you want to see those records too that requires addition criteria. If you need this and can't figure it out let us know.

This IIF solution is demonstrated in the attached database.