444,027 Members | 1,273 Online
Need help? Post your question and get tips & solutions from a community of 444,027 IT Pros & Developers. It's quick & easy.

# calculate works sundays if date/time include both date and time

 P: 59 In my table date/time field (starttime,endtime) include both date and time. How can I calculate worktime in query for each employee who works sundays? for an example: sometimes starttime and endtime is sunday and sometimes only endtime is sunday for an examlpe: eployee starttime endtime timeout patric Jo.. 12/03/2006 21:00:00 13/03/2006 02:00:00 01:00:00 patric jo.. 25/03/2006 10:00:00 25/03/200614:00:00 02:00:00 Nov 28 '06 #1
32 Replies

 Expert Mod 10K+ P: 14,534 In my table date/time field (starttime,endtime) include both date and time. How can I calculate worktime in query for each employee who works sundays? for an example: sometimes starttime and endtime is sunday and sometimes only endtime is sunday for an examlpe: eployee starttime endtime timeout patric Jo.. 12/03/2006 21:00:00 13/03/2006 02:00:00 01:00:00 patric jo.. 25/03/2006 10:00:00 25/03/200614:00:00 02:00:00 For example: Expand|Select|Wrap|Line Numbers   IIf(Weekday([endtime],1),[endtime] + 1, [endtime])   This function will return [endtime]+1(Monday) if [endtime] is a Sunday, else it will return [endtime]. Nov 28 '06 #2

 P: 59 For example: Expand|Select|Wrap|Line Numbers   IIf(Weekday([endtime],1),[endtime] + 1, [endtime])   This function will return [endtime]+1(Monday) if [endtime] is a Sunday, else it will return [endtime]. I have three cases for calculation sunday in my table 1 if date beginning on saturday and ending on sunday 2 if date beginning on sunday and ending on sunday 3 if date beginning on sunday and ending on monday for an example: peter jo.. 12/03/2006 07:00:00 12/03/2006 15:00:00 peter jo.. 11/03/2006 21:00.00 12/03/2006 03:00:00 peter jo.. 12/03/2006 22:00:00 13/03/2006 01:00:00 if 12/03/2006 is sunday how can I calculate all this cases in one function? Nov 29 '06 #3

 Expert Mod 10K+ P: 14,534 I have three cases for calculation sunday in my table 1 if date beginning on saturday and ending on sunday 2 if date beginning on sunday and ending on sunday 3 if date beginning on sunday and ending on monday for an example: peter jo.. 12/03/2006 07:00:00 12/03/2006 15:00:00 peter jo.. 11/03/2006 21:00.00 12/03/2006 03:00:00 peter jo.. 12/03/2006 22:00:00 13/03/2006 01:00:00 if 12/03/2006 is sunday how can I calculate all this cases in one function? I need more information. When and where do you want this calculation to take place. In a query, control on a form, etc? How is time out included in the calculation? What are you trying to calculate? No of days, hours, or what? Nov 30 '06 #4

 P: 59 I need more information. When and where do you want this calculation to take place. In a query, control on a form, etc? How is time out included in the calculation? What are you trying to calculate? No of days, hours, or what? I want to see this calculation first in query then in form. Time out isn't necessary be in calculation. I try to make query(for all employees) in which can be seen total work time on sundays as hours and minutes, for one month or year. Nov 30 '06 #5

 Expert Mod 10K+ P: 14,534 I want to see this calculation first in query then in form. Time out isn't necessary be in calculation. I try to make query(for all employees) in which can be seen total work time on sundays as hours and minutes, for one month or year. It's complicated. Try this ... Expand|Select|Wrap|Line Numbers   SELECT eployee, Sum(IIf(Weekday([starttime],1)=1,IIf(Weekday([endtime],1)=1, Hours([endtime]) - Hours([starttime]), 24 - Hours([starttime])), IIf(Weekday([endtime],1)=1,Hours([endtime]),0))) As SundayHours FROM YourTableNameHere GROUP BY eployee;   Nov 30 '06 #6

 Expert Mod 15k+ P: 31,615 Expand|Select|Wrap|Line Numbers SELECT Employee, StartTime, EndTime, EndTime-StartTime FROM YourTable WHERE (1 In(Weekday(StartTime),Weekday(EndTime))) This can be GROUPed or whatever, but should select the data correctly for you. Dec 1 '06 #7

 P: 59 Expand|Select|Wrap|Line Numbers SELECT Employee, StartTime, EndTime, EndTime-StartTime FROM YourTable WHERE (1 In(Weekday(StartTime),Weekday(EndTime))) This can be GROUPed or whatever, but should select the data correctly for you. you code is good if employee start and finish work on sunday, but I have case where employee start on saturday and finish on sunday. His worktime on sunday is time after midnight. Dec 3 '06 #8

 P: 59 It's complicated. Try this ... Expand|Select|Wrap|Line Numbers   SELECT eployee, Sum(IIf(Weekday([starttime],1)=1,IIf(Weekday([endtime],1)=1, Hours([endtime]) - Hours([starttime]), 24 - Hours([starttime])), IIf(Weekday([endtime],1)=1,Hours([endtime]),0))) As SundayHours FROM YourTableNameHere GROUP BY eployee;   this code return me message that the hours is not defined Dec 3 '06 #9

 Expert Mod 10K+ P: 14,534 this code return me message that the hours is not defined My apologies, I did this in a hurry. Hours should be Hour. Expand|Select|Wrap|Line Numbers   SELECT eployee, Sum(IIf(Weekday([starttime],1)=1,IIf(Weekday([endtime],1)=1, Hour([endtime]) - Hour([starttime]), 24 - Hour([starttime])), IIf(Weekday([endtime],1)=1,Hour([endtime]),0))) As SundayHours FROM YourTableNameHere GROUP BY eployee; Dec 3 '06 #10

 Expert Mod 15k+ P: 31,615 Expand|Select|Wrap|Line Numbers SELECT Employee, StartTime, EndTime, EndTime-StartTime FROM YourTable WHERE (1 In(Weekday(StartTime),Weekday(EndTime))) you code is good if employee start and finish work on sunday, but I have case where employee start on saturday and finish on sunday. His worktime on sunday is time after midnight. Barkarlo, You should try it out. It does work for any situation where either the start date OR the end date is a Sunday. It's a little unusual I know but that is sometimes necessary in code (for brevity). You can always redo it the long way with an OR if you prefer though. Dec 3 '06 #11

 P: 59 My apologies, I did this in a hurry. Hours should be Hour. Expand|Select|Wrap|Line Numbers   SELECT eployee, Sum(IIf(Weekday([starttime],1)=1,IIf(Weekday([endtime],1)=1, Hour([endtime]) - Hour([starttime]), 24 - Hour([starttime])), IIf(Weekday([endtime],1)=1,Hour([endtime]),0))) As SundayHours FROM YourTableNameHere GROUP BY eployee; yes that's it, thanks Dec 4 '06 #12

 Expert Mod 10K+ P: 14,534 yes that's it, thanks You're Welcome. Dec 5 '06 #13

 P: 59 You're Welcome. how can I see, in query, the workhours of an employee on holiday? Dec 5 '06 #14

 Expert Mod 10K+ P: 14,534 how can I see, in query, the workhours of an employee on holiday? I don't understand the question. Assuming employees on holiday don't have workhours. Holidays are dynamic and hard to query against. Mary Dec 8 '06 #15

 P: 59 I don't understand the question. Assuming employees on holiday don't have workhours. Holidays are dynamic and hard to query against. Mary I want to see workhours for state holiday not regular holiday Dec 8 '06 #16

 Expert Mod 15k+ P: 31,615 I want to see workhours for state holiday not regular holiday Barkarlo, While Mary's temporarily away can I step in here and ask for further clarification on her behalf. I can't see how your statement would clarify the situation. Maybe Mary will (She's always been better at understanding what people mean than I am) but just in case, it may help to explain a little more clearly. I can say that no Office system has intrinsic values for any holidays (state or otherwise) so it may be that what you're after is a step too far anyway. I expect that Mary will post in here when she returns anyway. Dec 8 '06 #17

 Expert Mod 10K+ P: 14,534 Thanks Ade. Barkolo, The only way you can do this is to create a table (lookup list) of all the state holidays concerned. Include it in the query in something like the following. Expand|Select|Wrap|Line Numbers IIf(nz(DLookup("[HolidayDate]", "tblHolidays", "[HolidayDate]=" & [StartTime] & " OR [HolidayDate]=" & [EndTime]),0)=0, , )   Mary Dec 9 '06 #18

 Expert Mod 15k+ P: 31,615 Mary, I have one thing to say - 'Dates'. Dec 9 '06 #19

 Expert Mod 10K+ P: 14,534 Mary, I have one thing to say - 'Dates'. Sorry Ade, as you said in a previous post. I was still tired and hungover when I did this. Should be ... Expand|Select|Wrap|Line Numbers IIf(nz(DLookup("[HolidayDate]", "tblHolidays", "[HolidayDate]=#" & [StartTime] & "# OR [HolidayDate]=#" & [EndTime] & "#"),0)=0, , )   Mary Dec 9 '06 #20

 P: 59 Sorry Ade, as you said in a previous post. I was still tired and hungover when I did this. Should be ... Expand|Select|Wrap|Line Numbers IIf(nz(DLookup("[HolidayDate]", "tblHolidays", "[HolidayDate]=#" & [StartTime] & "# OR [HolidayDate]=#" & [EndTime] & "#"),0)=0, , )   Mary I don't understand the end of your code .Is that a part of a table holiday or something else? Dec 9 '06 #21

 Expert Mod 10K+ P: 14,534 I don't understand the end of your code .Is that a part of a table holiday or something else? This is not code it's an indication to put whatever code you need to return the value if it's not a state holiday. Similarly, is where you put the code to return the value if it is a state holiday. Surrounding anything with <> means put in your own value. Mary Dec 9 '06 #22

 P: 59 This is not code it's an indication to put whatever code you need to return the value if it's not a state holiday. Similarly, is where you put the code to return the value if it is a state holiday. Surrounding anything with <> means put in your own value. Mary I wrote your code but return me following message " you may have entered an operand without an operator" Dec 10 '06 #23

 Expert Mod 10K+ P: 14,534 Can you post exactly what you've entered in full. Dec 10 '06 #24

 Expert Mod 15k+ P: 31,615 I wrote your code but return me following message " you may have entered an operand without an operator" No-one can tell what's wrong with your code unless they get to see it Barkarlo. Please indicate which line was yellow in the VBA window when you got the error message. Dec 10 '06 #25

 P: 59 Sorry Ade, as you said in a previous post. I was still tired and hungover when I did this. Should be ... Expand|Select|Wrap|Line Numbers IIf(nz(DLookup("[HolidayDate]", "tblHolidays", "[HolidayDate]=#" & [StartTime] & "# OR [HolidayDate]=#" & [EndTime] & "#"),0)=0, , )   Mary I have "tblholiday" with the following fields: No. holidaydate holidayname 1 and table "tblexploatation" employee starttime endtime Dec 11 '06 #26

 P: 59 Can you post exactly what you've entered in full. I have the table "tblholiday" with following fields: no. holiday date holiday name and "tblexploatation" employee starttime endtime date/time fields include both date and time I want to see in a query how much time does the employee work on holidays. Dec 11 '06 #27

 Expert Mod 10K+ P: 14,534 Barkolo If you don't post the query as it currently is we can't help you any further. Mary Dec 11 '06 #28

 P: 59 Barkolo If you don't post the query as it currently is we can't help you any further. Mary query is. Expand|Select|Wrap|Line Numbers   IIF(nz(dlookup("[holidaydate]","tblholiday", "[holidaydate]=#" &[starttime] & "# OR [holidaydate]=#" &[endtime] & "#"),0)=0,,) when I click on datasheet view return me already written message. After click OK show wrong in state holiday. Dec 11 '06 #29

 Expert Mod 10K+ P: 14,534 query is. IIF(nz(dlookup("[holidaydate]","tblholiday", "[holidaydate]=#" &[starttime] & "# OR [holidaydate]=#" &[endtime] & "#"),0)=0,,) when I click on datasheet view return me already written message. After click OK show wrong in state holiday. I've told you before, '' is not code. Expand|Select|Wrap|Line Numbers IIf(nz(Dlookup("[holidaydate]","tblholiday", "[holidaydate]=#" & [starttime] & "# OR [holidaydate]=#" & [endtime] & "#"),0)=0,IIf(Weekday([endtime],1),[endtime] + 1, [endtime]), )   also this is not actually a query it's just an if statement. Where do you want the resulting value? Mary Dec 12 '06 #30

 Expert Mod 15k+ P: 31,615 query is. Expand|Select|Wrap|Line Numbers   IIF(nz(dlookup("[holidaydate]","tblholiday", "[holidaydate]=#" &[starttime] & "# OR [holidaydate]=#" &[endtime] & "#"),0)=0,,) when I click on datasheet view return me already written message. After click OK show wrong in state holiday. That won't work. Mary has already explained that to you in post #22. Let me explain how this works Barkarlo. You ask questions about how to do something; we try to give help; you take that help and apply it (intelligently) to your code. Taking the explanation or example code and just dropping it in your database won't work. I see this as a problem already perfectly well answered. You need to read what has been posted for you and act on it. If, after that, you find you still have issues then come back for some more help. If it's not clear you need to replace with the value you need if it's not a state holiday. After the comma also needs the reverse value. This code returns a function - It also needs to be used or it's a waste of time completely. Dec 12 '06 #31

 P: 59 I've told you before, '' is not code. Expand|Select|Wrap|Line Numbers IIf(nz(Dlookup("[holidaydate]","tblholiday", "[holidaydate]=#" & [starttime] & "# OR [holidaydate]=#" & [endtime] & "#"),0)=0,IIf(Weekday([endtime],1),[endtime] + 1, [endtime]), )   also this is not actually a query it's just an if statement. Where do you want the resulting value? Mary I want to see,in query, only hours for an a employee who works on eastern holidays. sorry, but with your code can't get result. maybe I must more learn from book thanks for trying help me. Dec 12 '06 #32

 Expert Mod 15k+ P: 31,615 That seems sensible Barkarlo. Clearly we have difficulty communicating. Good luck. Dec 12 '06 #33