I'm trying to write a query that uses the time in another table which is set to EST and convert it to CST or MST depending on what the region is in another field in the same table.
Here is what I came up with but it dones't work. But basically I want if SPC2.ReceivingSite is STL, IRV, DAL then I want my new field to TimeLocal to be SPC2.CustCallBackTime(EST) - 1 and then -2 if the site is TSN or PHX
What I have below does not seem to work. Thx for any help.
Code:
TimeLocal: IIf([SPC2]![ReceivingSite] In ('STL','DAl','IRV'), TimeSerial(Hour([SPC2]![CustCallBackTime(EST)]-1),Minute([SPC2]![CustCallBackTime(EST)]), Second([SPC2]![CustCallBackTime(EST)])),
IIf([SPC2]![ReceivingSite] In ('PHX','TSN'), TimeSerial(Hour([SPC2]![CustCallBackTime(EST)]-2),Minute([SPC2]![CustCallBackTime(EST)]), Second([SPC2]![CustCallBackTime(EST)])), [SPC2]![CustCallBackTime(EST)]))