Syntax Help on Formula

[gatorfe]

I am working on a payroll report that calculates different shift rates per facility.  For facility 5,10,20 the shift rate for 1 is 0.  For facility 5,10 shift rate 2 and 3 is [Rate]*.06.  For facility 20 the shift rate for 2 is [Rate]*.35 and for shift rate 3 it is [Rate]*.45.  I created the following query but it is not validating.  Any suggestions would be greatly appreciated.  Thank you!

Total
(case when [Shift ID] = 1 and [Facility] in ('05','10','20') then 0
else
when [Shift ID] in (2,3) and [Facility] in ('05','10') then ([Rate]*.06)
else
when [Shift ID] = 2 and [Facility] = '20' then ([Rate]*.35)
else
when [Shift ID] = 3 and [Facility] ='20' then ([Rate]*.45)
end)

[jack_tka]

You could use the query like:


case
       when [Shift ID] = 1 and [Facility] in ('05','10','20') then [Shift Rate]=0
       when [Shift ID] in (2,3) and [Facility] in ('05','10') then [Shift Rate]=([Rate]*.06)
       when [Shift ID] = 2 and [Facility] = '20' then [Shift Rate]=([Rate]*.35)
       when [Shift ID] = 3 and [Facility] ='20' then [Shift Rate]=([Rate]*.45)
end



shift rate

[gatorfe]

Thanks.  That worked.