Recent content by 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.

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...

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...

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...

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.

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

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...

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.

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...

Good morning I tried that but I missed something for sure, but in the meanwhile I adopted your second suggestion and I Right Joint the other table and it works just perfect. Thanks very much for all your help!!

Good morning Thanks very much for your assistance, I tried the DLookup function but it didn't work then I tried to work around it: DLookUp("[Date]","[tblPublicHolidays]","[Date]= " & [Forms]![qryEventsGlobal]![EventDate]) but it didn't work either there must be something wrong with the way I'm...

Hi to everyone! I have a subform in datasheet view, based from a query which is a collection of events data during the years. I wanted to apply conditional formatting to highlight the Public Holidays. I have a table [tblPublicHolidays] listing the relevant public holidays dates throughout a...

HI Thanks for the reply I really appreciated, sorry for the delay from one reply to another but I'm busy on other tasks and I'm out of office most of the time. Please take a look at the attacked snapshot of the query I'm having trouble with. I got to this point using your suggestion (i.e...

Dear CJ_London I followed your advise I got an improvement for sure! but what I actually need is a list of events for a particular employee not for a single event at a time, so i tried to work around on your principle but it worked 50% of it. SELECT tblStaffInfo.StaffID, [LastName] & " " &...

thanks I'll give it a try and let you Know thanks