Expressions in a form

  • Thread starter Thread starter Don C
  • Start date Start date
D

Don C

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.
 
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.
Click to expand...

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".
 
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 said:
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.
Click to expand...

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".
Click to expand...
 
Back
Top