Select Case "Priorities"!?

[Shoutaro]

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

 

[arnelgp]

what is your Regional setting?
is sunday the firstday of your week?

maybe:

([EventDate]=[tblPublicHolidays].[Date]) And (Weekday([EventDate], 1)=1),[TotalTime]*2

 

[Shoutaro]

Hi, thanks for your reply!
the region is OK because on any other Sunday it works, what i really want is that it sops evaluating the other case statements once it finds a the first correct statement. I also tried a nested IIf statement but with no success.

 

[Gasman]

Use a Case statement in a UDF?

Key is

EventDate]=[tblPublicHolidays].[Date]

where do you get [tblPublicHolidays].[Date] from?
Inspect all values.

 

[Shoutaro]

Hi, I get the data from a table listing the public holidays in my country. it does work since when it happens a public holiday it evaluates the correct case statement. my problem is that when a public holiday occurs on a Sunday, I need the select case function to evaluate the Sunday case and not the Public Holiday.

 

[isladogs]

Hi, thanks for your reply!
the region is OK because on any other Sunday it works, what i really want is that it sops evaluating the other case statements once it finds a the first correct statement. I also tried a nested IIf statement but with no success.

As you say it stops on the first correct statement.
So change the order in order to make Access prioritise the way you want it to.

 

[Shoutaro]

that what I've done but it is not stopping on the first correct statement, that's my problem.

 

[isladogs]

It will do if your code is correct.
However its difficult to follow your code with no context

 

[arnelgp]

reverse the logic and add more test:

[EventDate]=[tblPublicHolidays].[Date] And (Weekday([EventDate])<>1,[TotalTime]*3,

 

[Shoutaro]

OK my apologies I am talking on a select case statement while in reality I've used the switch statement, I hope that it follows the same rules of the select case.

 

[Gasman]

OK my apologies I am talking on a select case statement while in reality I've used the switch statement, I hope that it follows the same rules of the select case.

According to https://www.excelfunctions.net/vba-switch-function.html it is meant to.

However if you cannot get it to work, I'd just write a simple function to return the value/multiiplier, whetever you need.

HTH

 

[isladogs]

However you do it, you must get the order correct.
If you want a public holiday on a Sunday to be treated as a Sunday, put that condition before testing for holidays.
Or vice versa if you want the opposite.

 

[Shoutaro]

Good morning

this is the order of the switch function, as you can see the first three steps evaluate for Sundays, while the fourth step is for Public holidays. but when ever there is a public holiday this is taking priority no matter the first three options??
Switch(((
1. Weekday([EventDate])=1) And ([TotalTime]>[MaxOfVL])),([TotalTime]-[BreakD])*2,
2. Weekday([EventDate])=1,[TotalTime]*2,
3. ([EventDate]=[tblPublicHolidays].[Date]) And (Weekday([EventDate])=1),[TotalTime]*2,
4. [EventDate]=[tblPublicHolidays].[Date],[TotalTime]*3,
5. ([EventDes]="Time in lieu consumed" And ([TotalTime]>[MaxOfVL])),([TotalTime]-[BreakD])*1,
6. [EventDes]="Time in lieu consumed",[TotalTime]*1,
7. [TotalTime]>[MaxOfVL],([TotalTime]-[BreakD])*1.5,
8. [TotalTime]<[MaxOfVL],[TotalTime]*1.5)

 

[Minty]

I'm not sure how this helps but your 3rd check is redundant as it will never be reached if it's a Sunday due to rule 2.

EDIT : You could add AND Weekday([EventDate])<>1 to force it to check it's NOT as Sunday but it shouldn't ever get that far.

 

[Shoutaro]

Hi, yes in fact I added the third check later to try to solve this issue and when I've changed to AND Weekday([EventDate])<>1 it stopped to that check (I mean the third check and evaluated its multiplier but it seam that it is not recognizing that it is a Sunday while on any another Sunday it works!???

 

[Minty]

Does your EventDate contain a time element? That would cause it not to match your Holiday.date ?

Also just in case - Date is a reserved word and a poor choice for your Holiday table field name, change it to HolDate or similar, it could well be causing a problem.

 

[Shoutaro]

This is the whole SQL of this query if it can help

SELECT tblStaffInfo.StaffID, tblStaffInfo.LastName, tblStaffInfo.FirstName, qryEvents.EventDate, tblEventDes.EventDes, qryEvents.StartTime, qryEvents.EndTime, ([EndTime]-[StartTime]+([EndTime]<[StartTime]))*24 AS TotalTime,
Switch(((Weekday([EventDate])=1) And ([TotalTime]>[MaxOfVL])),([TotalTime]-[BreakD])*2,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) AS PayHours, qryEvents.Verified, qryEvents.Comments, Min(qryStaffRosterChange.RosterCode) AS Roster, Max(qryStaffRosterChange.VL) AS MaxOfVL, tblPublicHolidays.Date, tblEventDes.EventAbr
FROM (((tblStaffInfo INNER JOIN qryEvents ON tblStaffInfo.StaffID = qryEvents.StaffID) INNER JOIN qryStaffRosterChange ON (tblStaffInfo.StaffID = qryStaffRosterChange.StaffID) AND (qryEvents.YDate = qryStaffRosterChange.YDate)) LEFT JOIN tblEventDes ON qryEvents.EventDesID = tblEventDes.EventDesID) LEFT JOIN tblPublicHolidays ON qryEvents.EventDate = tblPublicHolidays.Date
WHERE (((qryEvents.EventDate)>=[YearOfChange]))
GROUP BY tblStaffInfo.StaffID, tblStaffInfo.LastName, tblStaffInfo.FirstName, qryEvents.EventDate, tblEventDes.EventDes, qryEvents.StartTime, qryEvents.EndTime, ([EndTime]-[StartTime]+([EndTime]<[StartTime]))*24, qryEvents.Verified, qryEvents.Comments, tblPublicHolidays.Date, tblEventDes.EventAbr, qryEvents.EventDesID, qryStaffRosterChange.BreakD, tblPublicHolidays.Date
HAVING (((qryEvents.EventDesID) In (6,7,11)))
ORDER BY tblStaffInfo.LastName, tblStaffInfo.FirstName, qryEvents.EventDate;

 

[Shoutaro]

Hi Minty,
yes the EventDate contains date/time data type and for public holiday I have a table listing my country public holidays that the query checks it. what I cannot understand is why the check for public holiday is getting the priority.

 

[arnelgp]

you may need to analyze this:

sunday = 1 (weekday)

1. you may have sunday (not holiday) on your query
weekday([eventdate]) = 1 and nz([tblPublicHolidays].[Date], 1) = 1


2. you may have holiday (not sunday) on your query
weekday([eventdate]) <> 1 and nz([tblPublicHolidays].[Date], 1) <> 1


3. you may have holiday at same time sunday on your query.
weekday([eventdate]) = 1 and nz([tblPublicHolidays].[Date], 1) <> 1

**
im using NZ since on your query you use Left join and there
might not be a date (Null) on tblPublicHolidays.

 

[Minty]

The only obvious reason is that for some reason WeekDay(eventdate) isn't evaluating to 1

Debugging: Build a simple query with your event date and left joined to your holiday table and add a couple of calculated fields something like;

MyWeekDay : Weekday([EventDate])
Holiday : IIf ([EventDate]=[tblPublicHolidays].[Date], "They Match", "NoMatch")

And see if that is actually giving you what you expect to see.

Edit - I meant does your event date always = 10/01/2019 00:00 or is there a time element 10/01/2019 21:23:03 ??