Query with an OR statement in Criteria

[GrandMasterTuck]

Hi folks. I'm trying to get a list of employees to print out correctly. I have an employees table, and each employee has a possible four different shifts I can assign them: MIDS, DAYS, NOONS and SWNG (swing shift, half on Days and half on Noons).

I tried to create a query that filters my employee list based on which shift the manager works. So if the manager comes in on Days, he can get a list of the people who have DAYS or SWNG in their shift column, and the afternoon manager can get AFTS or SWNG people, but the midnight boss ONLY GETS MIDS and not the SWNG folks.

To do this, I have a form with a SHIFT combo-box. The combo-box has three values to pick from, DAYS, AFTS and MIDS. The boss selects one of them, and the subform below auto-fills with a list of his available employees for that shift (as spelled out in the second paragraph above). The form works PERFECTLY ... for MIDS only. DAYS and AFTS it's always blank, no records come up.

Here are some examples of what I tried in the Criteria column:

[Forms]![frmSchedule]![SchedShift] Or 'SWNG'
="'" & [Forms]![frmSchedule]![SchedShift] & "' Or 'SWNG'"

I even tried doing an IF statement in there which checks to see what the value of the shift box is, and if it's MIDS, just return the value "MIDS" but if it's anything else, to string the shift box value together with that " Or 'SWNG'" part after it, and write that to a text box, and then use the text box as the criteria for the query. Still only works with MIDS, I think because there isn't an Or statement in it.

So I'm thinking I'm screwing up the syntax. Any ideas on what I can try next? I gotta get this fixed! Thanks again!

 

[arnelgp]

instead of filtering set the subform's recordsource on the AfterUpdate event of your combo:

private sub combo_afterupdate()
me.subformname.form.recordsource = "select * from table where [shift field] In ('" & Me.combo.Value & "', 'SWNG')"
end sub