selct query to find elapsed time

[SlinkRN]

Hi,
My boss wants to know the total time between admit of a patient and birth of her baby. This information is in an Access program and I can do a select query to find it, but I can't figure out how to do it In the database, the admit date, admit time, delivery date, and delivery time are all separate fields. In the query I made a new field called "AdmitToDel" but I don't know how to get the difference since the date and times are in separate fields. I'm more of an Excel person myself so I'm always lost in Access. Can anyone help? I've done a search and I've looked through my Helen Feddema book but can't find an answer. Thanks! Slink

 

[Andrew Fergus]

Hi
To get the elapsed days, use something like this in a query :

Days : DateDiff("d", [AdmitDate], [DeliveryDate])-IIf([DeliveryTime] < [AdmitTime], 1, 0)

Make sure you use your actual field names.

To get the hours (over and above the days), use this in the same query :
Hours : (DateDiff("n", [AdmitTime], [DeliveryTime]) + IIf([DeliveryTime] < [AdmitTime], 1440, 0))/60

To merge the 2 parts into one field, you could use this instead :
Combined: DateDiff("d", [AdmitDate], [DeliveryDate]) - IIf([DeliveryTime] < [AdmitTime], 1, 0) & " Days, " & Format((DateDiff("n", [AdmitTime], [DeliveryTime]) + IIf([DeliveryTime] < [AdmitTime], 1440, 0))/60, "0.0") & " Hours"

If you want to break the hours down into hours and minutes then have a go working out the difference between the hours part of the time and the minutes part of the time.

HTH, Andrew

 

[SlinkRN]

Thanks Andrew! I'll give that a try next time I'm at the hospital

 

[SlinkRN]

Thanks so much Andrew! It worked perfectly Slink