IIF in Query Q - Listing All Entries

[stahl]

Hello,

I currently have a DB that has a field in a table that records which shift (A, B, or C) that some data was collected on. I am trying to write a Query in order to filter the results so that either the data from shifts A,B, or C show up, or if "All Shifts" is selected, then all the data shows up. I have another table set up that records the shift selection from a form. I was trying to use an Iif statement but it does not seem to be working for the "All Shifts" option. Selecting "Shift A" or "Shift B" etc. works fine.

IIf([ShiftSelect]="All Shifts","Shift [ABC]",[ShiftSelect])

**[ShiftSelect] containts the users selection for which shift they want to view.

I have also tried this with a wildcard statement (Like "*") instead of "Shift [ABC]", to see if I could get any data to show but I still get nothing when "All Shifts" is selected!

Let me know if you can see the problem, or know another way to go about this query!

 

[EMP]

You can put the criteria in a new column in the query grid like this.

------------------------------------
Field: IIf([ShiftSelect]="All Shifts", True, [FieldName]=[ShiftSelect])

Show: uncheck

Criteria: True
------------------------------------


See Jon K's thread in the sample database forum
http://www.access-programmers.co.uk/forums/showthread.php?t=103312

^

 

[stahl]

Brilliant!

Works perfectly! Thanks for your help. I never even thought about putting the Iif statement in the field!