Good Sunday to every one,
I have a query that calculated the working hours and assigns a multiplier according the day (Sunday/Public Holiday), overtime etc..
in one of the fields I have an expression with a select case statement:
PayHours: Switch(((Weekday([EventDate])=1) And ([TotalTime]>[MaxOfVL])),([TotalTime]-[BreakD])*2,Weekday([EventDate])=1,[TotalTime]*2,([EventDate]=[tblPublicHolidays].[Date]) And (Weekday([EventDate])=1),[TotalTime]*2,[EventDate]=[tblPublicHolidays].[Date],[TotalTime]*3,([EventDes]="Time in lieu consumed" And ([TotalTime]>[MaxOfVL])),([TotalTime]-[BreakD])*1,[EventDes]="Time in lieu consumed",[TotalTime]*1,[TotalTime]>[MaxOfVL],([TotalTime]-[BreakD])*1.5,[TotalTime]<[MaxOfVL],[TotalTime]*1.5)
my issue is, I want that if an event happens on a Sunday that also happens to be a public holiday (the part in bold) thus it condition but no matter how I tried to play with this expression it is always the Public holiday case that gets priority (the one underline):banghead::banghead:
any suggestions please?
thanks
I have a query that calculated the working hours and assigns a multiplier according the day (Sunday/Public Holiday), overtime etc..
in one of the fields I have an expression with a select case statement:
PayHours: Switch(((Weekday([EventDate])=1) And ([TotalTime]>[MaxOfVL])),([TotalTime]-[BreakD])*2,Weekday([EventDate])=1,[TotalTime]*2,([EventDate]=[tblPublicHolidays].[Date]) And (Weekday([EventDate])=1),[TotalTime]*2,[EventDate]=[tblPublicHolidays].[Date],[TotalTime]*3,([EventDes]="Time in lieu consumed" And ([TotalTime]>[MaxOfVL])),([TotalTime]-[BreakD])*1,[EventDes]="Time in lieu consumed",[TotalTime]*1,[TotalTime]>[MaxOfVL],([TotalTime]-[BreakD])*1.5,[TotalTime]<[MaxOfVL],[TotalTime]*1.5)
my issue is, I want that if an event happens on a Sunday that also happens to be a public holiday (the part in bold) thus it condition but no matter how I tried to play with this expression it is always the Public holiday case that gets priority (the one underline):banghead::banghead:
any suggestions please?
thanks