qry Number of Patients at Midnight 1

[dhborchardt]

I need a query that will find out how many patients were in the hospital at midnight of a given day or range of days. I have the admitDateTime and the DischargeDateTime. The format of the Admit and Discharges is
'3/31/2008 12:00:00 AM'.
I would like to use a parameter query and put either one date in or a range of dates.

 

[lespaul]

[tt]
PatientID AdmitDateTime DischargeDateTime
1 3/29/2008 08:00:00 am 3/31/2008 11:15:00 am
2 3/30/2008 12:01:00 pm
3 3/30/2008 13:45:00 pm
4 3/30/2008 22:15:00 pm
5 3/31/2008 01:02:00 am
[/tt]

So with this data you would need to know that 4 people were in the hospital at midnight on 3/30/08?

something like this may work for a single date:

WHERE AdmitDateTime >= [Enter Date] + " 00:00:00 AM" AND DischargeDateTime < [Enter Date] + " 00:00:00 AM"

I don't think you'll be able to get multiple dates unless you use VBA to loop through the parameter and extract each date.

Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com

 

[dhborchardt]

Here is what I have so far:

SELECT dbo_AdmittingData.AdmitDateTime, dbo_AdmittingData.VisitID, dbo_AdmVisits.PatientID, dbo_MriPatients.Name, dbo_AdmDischarge.DischargeDateTime
FROM ((dbo_AdmDischarge INNER JOIN dbo_AdmittingData ON dbo_AdmDischarge.VisitID = dbo_AdmittingData.VisitID) INNER JOIN dbo_AdmVisits ON dbo_AdmittingData.VisitID = dbo_AdmVisits.VisitID) INNER JOIN dbo_MriPatients ON dbo_AdmVisits.PatientID = dbo_MriPatients.PatientID
WHERE AdmitDateTime >= [Enter Date] + " 00:00:00 AM" AND DischargeDateTime < [Enter Date] + " 00:00:00 AM";

I don't get any errors but it comes back with no records and I know there were patients. I tried three different days.

I guess my end goal will be to use a range of dates and have the function count how many for each date.

 

[PHV]

Replace this:
WHERE AdmitDateTime >= [Enter Date] + " 00:00:00 AM" AND DischargeDateTime < [Enter Date] + " 00:00:00 AM";
with this:
WHERE AdmitDateTime <= [Enter Date] AND (DischargeDateTime > [Enter Date] OR DischargeDateTime Is Null);

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[PHV]

Or perhaps this:
WHERE AdmitDateTime <= ([Enter Date] + #23:59:59#) AND (DischargeDateTime > ([Enter Date] + #23:59:59#) OR DischargeDateTime Is Null);

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886