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
|
|||
|
|||
Datediff Function
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. I forgot to say I have an entry of DATE WORKED with the Format set to Short Date and Control set tot DATE WORK on the form also, but because I am a new user of Access I don’t Know how to write the Datedif expression to include the DATE WORKED in the Datedif expression and if I need to have 12:00 AM, IN to 12:00 AM, OUT, returned as 24 in the HOURS. -- Don C |
#2
|
|||
|
|||
Datediff Function
Understand that the date/time datatype hold both values even though you are
only displaying the time. So what Access sees is: 12/30/1899 7:30 PM 12/30/1899 12:00 AM which is 0 days and the time, so it is displaying the correct answer. What you meant to say is: 01/31/09 7:30 PM 02/01/09 12:00 AM and that difference is 4.5 hours. You need to enter the date AND time in the field if you want correct answers. -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.accessmvp.com "Don C" wrote in message ... 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. I forgot to say I have an entry of DATE WORKED with the Format set to Short Date and Control set tot DATE WORK on the form also, but because I am a new user of Access I don't Know how to write the Datedif expression to include the DATE WORKED in the Datedif expression and if I need to have 12:00 AM, IN to 12:00 AM, OUT, returned as 24 in the HOURS. -- Don C |
Thread Tools | |
Display Modes | |
|
|