Date / Time question

[mmohon]

Ok, I want to look at volume in the ER for given times of the day.
I have Patient Entry and Exit times, In Date:Time format

So basically I'm going to need something like:
Count Patients if 1AM is between EntryTime And Exit Time

I want to look at a months average

What I'm running into is, since its in date:time format I might get an
Entry at 10PM, and exit at 1AM the next day. I have to figure out how
to properly strip the date out of the information, yet still show that
12AM is between their 10PM entry, and 1PM Exit.

Anyone have any suggestions?

 

[Guest]

I'd suggest putting a simple function in a standard module in the database:

Public Function AtTime(dtmIn As Date, dtmOut As Date, tmTimeAt As Date) As
Boolean

Dim tmIn As Date, tmOut As Date

tmIn = TimeValue(dtmIn)
tmOut = TimeValue(dtmOut)

' does time range span midnight
If Day(dtmIn) < Day(dtmOut) Then
' if so is time out on or after the time
If tmOut >= tmTimeAt Then
AtTime = True
End If
Else
' times are on same day so
' is the time within time range
If tmIn <= tmTimeAt And tmOut >= tmTimeAt Then
AtTime = True
End If
End If

End Function

You can then call it, e.g. in a query to find the number of patients in the
ER in the current month and present at 1:00 AM:

SELECT COUNT(*)
FROM ER_Admissions
WHERE
((YEAR(EntryTime) = YEAR(DATE())
AND MONTH(EntryTime) = MONTH(DATE()))
OR (YEAR(ExitTime) = YEAR(DATE())
AND MONTH(ExitTime) = MONTH(DATE())))
AND AtTime(EntryTime,ExitTime, #1:00 AM#);

Note that if you used the above for a year/month other than the current one
the result would be slightly inaccurate as those patients admitted on the
last day of the month and present at 1:00 AM on the first day of the next
month would be counted. You could correct this by using another query to get
the count of patients in the ER at 1:AM on the first day of the following
month with another query and subtracting the result from the result of the
first query.

Incidentally don't be missed into thinking that the time values computed in
the above code are times without a date. They are in fact times on 30
December 1899, which is day zero in Access's date/time implementation.
However, the date is irrelevant when doing the time comparisons.

Ken Sheridan
Stafford, England