Multiple criteria to consider to return a value in a query

[jpkeller55]

[FONT=&quot]I am trying to write an IIf statement that considers multiple criteria to return a value of y or n based on day of week and time of day to determine if an event would be considered "on-call". There are multiple facilities (three) and the rules are slightly different for each each facility (branch). For all events occurring on a Sat or Sun (DayOfWeek = 1 or 7) and regardless of what facility, the event would be considered "on-call" . For facilities #1 and #20, an event after 17:59:59 during the week (DayOfWeek = 2,3,4,5,6) would be considered "on-call". For facilities #21 an event after 15:59:59 during the week (DayOfWeek = 2,3,4,5,6 "on-call .

Attached is a screen shot of sample data and how the last column [OnCall] should work. Below is the start of my attempt to get the code to work.
[/FONT]

[FONT=&quot][FONT=&quot]ONCALL: IIf([OrderDayOfWeek]=1 Or [OrderDayOfWeek]=7,"y","n") [/FONT][/FONT]

Appreciate any help...Thanks, Jim

 

[plog]

First, you should only have 1 field to capture the Date/Time of an order. OrderDate and OrderTime shouldn't be seperate. You need to get those into the same field.

For your issue, you can either make a table to define your On-Call times for each facility and use SQL to determine if a time falls within that period or not. Or you could build a custom function--you would pass that function the facility and the Date/Time and it would return Y or N to determine if it was OnCall or not.

Depending on how many facilities you have, if its just 3, I would build that function and put the logic in there.

 

[jpkeller55]

OK, thanks for the advice. I will see what I can come up with.