If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Expressions in a form
I have a form for Employee’s Time Card. I have an entry for DATE WORKED, TIME
IN, TIME OUT, and HOURS WORKED. When I enter the DATE WORKED, I enter it as a medium date. Example: 12/13/08 and the TIME IN as a medium time of 04:00 PM and TIME OUT as a medium time of 12:00 PM. In the HOURS WORKED I use this expression: Datediff(“n”,[TIME IN],[TIME OUT])/60 which gives me the hours worked. Since the TIME IN really is 12/30/1899 4:00 PM and the TIME OUT is 12/30/1899 12:00 PM, the HOURS WORKED returns -16 which should be 8. I know -16 is right because of the TIME IN and the TIME OUT preceding date being 12/30/1899. I was wondering if it is possible to write a expression for TIME IN and TIME OUT that will use the date in the DATE WORKED that I enter and still let me enter the time to TIME IN and TIME OUT. If this is possible I would like to know how. If it is not possible I would like to know how you would set it up. Please help me. -- Don C |
#2
|
|||
|
|||
Expressions in a form
On Thu, 5 Feb 2009 21:57:00 -0800, Don C
wrote: I was wondering if it is possible to write a expression for TIME IN and TIME OUT that will use the date in the DATE WORKED that I enter and still let me enter the time to TIME IN and TIME OUT. If this is possible I would like to know how. If it is not possible I would like to know how you would set it up. Please help me. By far the simplest approach would be to store the date-time in and the date-time out. #2/4/2009 20:00# is in fact eight hours before #2/5/2009 04:00#, and DateDiff will get it right. If you choose not to store the actual time started and time ended (which logically includes the date; 4 am this morning is a different point in time than 4 am yesterday!), then you'll need to make the assumption that a workshift will never, ever exceed 24 hours, so you can use: DateDiff("n", [Time In], [Time Out]) / 60.+IIF([Time In] [Time Out], 24, 0) to "wrap around midnight". -- John W. Vinson [MVP] |
#3
|
|||
|
|||
Expressions in a form
Thanks John! The Datediff expression that you gave me worked. I probably will
ask you some questions about reports if I cant figure them out on my own. Thanks again. -- Don C "John W. Vinson" wrote: On Thu, 5 Feb 2009 21:57:00 -0800, Don C wrote: I was wondering if it is possible to write a expression for TIME IN and TIME OUT that will use the date in the DATE WORKED that I enter and still let me enter the time to TIME IN and TIME OUT. If this is possible I would like to know how. If it is not possible I would like to know how you would set it up. Please help me. By far the simplest approach would be to store the date-time in and the date-time out. #2/4/2009 20:00# is in fact eight hours before #2/5/2009 04:00#, and DateDiff will get it right. If you choose not to store the actual time started and time ended (which logically includes the date; 4 am this morning is a different point in time than 4 am yesterday!), then you'll need to make the assumption that a workshift will never, ever exceed 24 hours, so you can use: DateDiff("n", [Time In], [Time Out]) / 60.+IIF([Time In] [Time Out], 24, 0) to "wrap around midnight". -- John W. Vinson [MVP] |
Thread Tools | |
Display Modes | |
|
|