Switch in Criteria

[donnatronious]

I have query on a table with a field PayTypeNo that I am trying too specify criteria based on a parameter. The first code I have listed runs fine, however the second returns 0 rows when parameter 7201 is specified. Anyone know why. This code is listed in the criteria area of the PayTypeNo field in the query grid in Access.

Switch([DATES & DEPARTMENT]![DEPARTMENT]="7201","REGULAR",[DATES & DEPARTMENT]![DEPARTMENT]="5500","OVERTIME")

Switch([DATES & DEPARTMENT]![DEPARTMENT]="7201","REGULAR" or "OVERTIME",[DATES & DEPARTMENT]![DEPARTMENT]="5500","OVERTIME")

 

[scriverb]

Your first Switch function statement confirms to the accepted syntax. Matching Expressions followed by a value to be returned if true. The second Switch statement does not.

Switch([DATES & DEPARTMENT]![DEPARTMENT]="7201",[red]"REGULAR" or "OVERTIME"[/red],[DATES & DEPARTMENT]![DEPARTMENT]="5500","OVERTIME")

This return answer is saying to return either "REGULAR" or "OVERTIME". You can't have it that way. It is one or the other but not as a value to be returned.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[donnatronious]

Ok, but when the parameter 7201 is passed, I need all rows who have regular and overtime. How should I try and accomplish this?