Datedif Expression

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

Don C

I have 3 entries on a form: TIME IN, TIME OUT, HOURS. In the TIME IN, I set
the Format as: Medium Time with an Input Mask for Medium Time. For TIME OUT,
I set the Format as: Medium Time with an Input Mask for Medium Time. For
HOURS, I set the Format as: Standard Number with this Control: Datedif(“nâ€
,[TIME IN],[TIME OUT])/60.

When I enter TIME IN as 12:00 AM (Mid Night), and TIME OUT as 07:30 AM, the
HOURS returns 7.5 which is right, but when I enter TIME IN as 07:30 PM, and
TIME OUT as 12:00 AM (Mid Night), the HOURS returns -19.5. It should be 4.5.
Also when I enter TIME IN as 12:00 AM and TIME OUT as 12:00 AM, I want HOURS
to return 24. This problem is confusing me. Can you help me?
 
datediff will work fine - but you need to add the date or how will your
application know which day your using

12midnight to 12 midnight = 0 hours & 0 mins

but

12midnight today to 12 midnight tomorrow = 24 hours & 0 mins

The input masks and formats don't affect the data - just how it's entred and
seen.

May be an idea (to keep it simple) just to add an option group
Yesterday/today/tomorrow
but this may not be a good idea ?? as don't really understand your application

have a look at
http://www.mvps.org/access/datetime/date0008.htm
and
http://www.mvps.org/access/datetime/date0009.htm
 
Back
Top