Calculating time

[SlinkRN]

I'm new to Access, so this question may be very basic - sorry. I have a database that has a table which is entered into by a form. I want to calculate "TimeInHospital" (in minutes) which is "TimeOfDischarge" minus "TimeOfAdmit". These times are all less than a day - usually just an hour or two. I'd like to use military time to enter admit and discharge time (which I did by using short time). I made a query and used the formula builder to build the formula to subtract admit time from discharge time. The result of the calculation is way off the way I'm doing it. Is there a special way of calculating military time? BTW, it didn't work when I used regular time either. Can anyone help?

 

[dk]

Hi,

First of all, a bit about how Access stores date values. All dates are stored as a numeric value with 0 being 30th December 1899, 1 being 31st December 1899, 2 being 1st January 1900 and so on. Time of day is stored as a fractional part of the number e.g. :-

37838 represents midnight on the 5th August 2003. 37838.5 represents midday on the 5th August 2003, etc.

When you subtract one date from another you will end up with a number which represents the number of days between the two dates. If the two dates are the same day then you will end up with a fractional number.

The easiest way to return the number of minutes between two date/time values is to use the DateDiff function (see VBA help for full description of this). Try something like this in your query:-

TimeInHospital: DateDiff("n",[TableName]![TimeOfAdmit],[TableName]![TimeOfDischarge])

 

[SlinkRN]

Thanks so much! That worked. Thank you for explaining why it wasn't working. It helps to know how Access does these things. I know I asked for the answer in minutes, but now looking at the result . . . Is there a way to have it come up in hours and minutes. So instead of 250 minutes, it would come up as 4:10 or 4 hrs 10 mins?

PS: What does the "n" do?

 

[dk]

Hi,

You can use the TimeSerial to do this. TimeSerial takes an Hour, Minute and Second argument and returns a date/time value. Incorporate the original formula into the TimeSerial and you should be on your way...

TimeInHospital: TimeSerial(DateDiff("h",[Admissions]![TimeOfAdmit],[Admissions]![TimeOfDischarge]),DateDiff("n",[Admissions]![TimeOfAdmit],[Admissions]![TimeOfDischarge]),0)

Check the VBA help on TimeSerial and DateDiff to work out how these work (and to answer your question about what 'n' does).