Between Dates Parameters missing a day

L

Linda RQ

Hi Everyone,

Access 2003. I use the query grid to create my query but pasted the sql
below so if the answer can be directed so I can use the grid, that would
help.

I need my query to display a list of patients who were on a therapy 70 or 81
in the picu. It is working great except, I entered these dates...Start Date
7/1/07, End Date 9/30/07. The problem is, it is not showing 2 records who
have end dates of 9/30/07. If I type in Start Date 7/1/07 and End Date
10/1/07 they are included on that. It wouldn't make sense to the user to
type in 10/1/07 because they are looking for dates between 7/1 and 9/30.

Thanks,
Linda

PARAMETERS [Enter Start Date] DateTime, [Enter End Date] DateTime;
SELECT tblPatients.PtLName, tblPatients.PtID, tblPatients.AdmitNum,
tblPtThpy.PtThpyID, tblAreaList.AreaName, tblTherapyType.TherapyTypeID,
tblTherapyType.TherapyDesc, tblPtThpy.ThpyStDtTm, tblPtThpy.ThpyEndDtTm,
IIf([ThpyStDtTm]<[Enter Start Date],[Enter Start Date],[ThpyStDtTm]) AS
CalcStartDate, Nz([ThpyEndDtTm],[Enter End Date]) AS CalcEndDate,
Nz([ThpyEndDtTm],[Enter End Date])-IIf([ThpyStDtTm]<[Enter Start
Date],[Enter Start Date],[ThpyStDtTm]) AS VentLOSDays, [VentLOSDays]*24 AS
VentLOSHours
FROM (tblPatients INNER JOIN (tblAreaList INNER JOIN tblPtLocation ON
tblAreaList.AreaID=tblPtLocation.AreaID_fk) ON
tblPatients.PtID=tblPtLocation.PtID_fk) INNER JOIN (tblTherapyType INNER
JOIN tblPtThpy ON tblTherapyType.TherapyTypeID=tblPtThpy.ThpyTypeID_fk) ON
tblPatients.PtID=tblPtThpy.PtID_fk
GROUP BY tblPatients.PtLName, tblPatients.PtID, tblPatients.AdmitNum,
tblPtThpy.PtThpyID, tblAreaList.AreaName, tblTherapyType.TherapyTypeID,
tblTherapyType.TherapyDesc, tblPtThpy.ThpyStDtTm, tblPtThpy.ThpyEndDtTm
HAVING (((tblAreaList.AreaName)="picu") AND
((tblTherapyType.TherapyTypeID)=70 Or (tblTherapyType.TherapyTypeID)=81) AND
((Nz([ThpyEndDtTm],[Enter End Date])) Between [Enter Start Date] And [Enter
End Date]))
ORDER BY tblPatients.PtLName;
 
G

Guest

I'm not sure why this is a totals query when there are no aggregated values.
You might want convert your Date+Time values to just the Date with
DateValue([YourDateField])

PARAMETERS [Enter Start Date] DateTime, [Enter End Date] DateTime;
SELECT tblPatients.PtLName, tblPatients.PtID, tblPatients.AdmitNum,
tblPtThpy.PtThpyID, tblAreaList.AreaName, tblTherapyType.TherapyTypeID,
tblTherapyType.TherapyDesc, tblPtThpy.ThpyStDtTm, tblPtThpy.ThpyEndDtTm,
IIf([ThpyStDtTm]<[Enter Start Date],[Enter Start Date],[ThpyStDtTm]) AS
CalcStartDate, Nz([ThpyEndDtTm],[Enter End Date]) AS CalcEndDate,
Nz([ThpyEndDtTm],[Enter End Date])-IIf([ThpyStDtTm]<[Enter Start
Date],[Enter Start Date],[ThpyStDtTm]) AS VentLOSDays, [VentLOSDays]*24 AS
VentLOSHours
FROM (tblPatients INNER JOIN (tblAreaList INNER JOIN tblPtLocation ON
tblAreaList.AreaID=tblPtLocation.AreaID_fk) ON
tblPatients.PtID=tblPtLocation.PtID_fk) INNER JOIN (tblTherapyType INNER
JOIN tblPtThpy ON tblTherapyType.TherapyTypeID=tblPtThpy.ThpyTypeID_fk) ON
tblPatients.PtID=tblPtThpy.PtID_fk
WHERE tblAreaList.AreaName="picu" AND
tblTherapyType.TherapyTypeID IN (70,81) AND
Datevalue(Nz([ThpyEndDtTm],[Enter End Date])) Between [Enter Start Date] And
[Enter
End Date];
--
Duane Hookom
Microsoft Access MVP


Linda RQ said:
Hi Everyone,

Access 2003. I use the query grid to create my query but pasted the sql
below so if the answer can be directed so I can use the grid, that would
help.

I need my query to display a list of patients who were on a therapy 70 or 81
in the picu. It is working great except, I entered these dates...Start Date
7/1/07, End Date 9/30/07. The problem is, it is not showing 2 records who
have end dates of 9/30/07. If I type in Start Date 7/1/07 and End Date
10/1/07 they are included on that. It wouldn't make sense to the user to
type in 10/1/07 because they are looking for dates between 7/1 and 9/30.

Thanks,
Linda

PARAMETERS [Enter Start Date] DateTime, [Enter End Date] DateTime;
SELECT tblPatients.PtLName, tblPatients.PtID, tblPatients.AdmitNum,
tblPtThpy.PtThpyID, tblAreaList.AreaName, tblTherapyType.TherapyTypeID,
tblTherapyType.TherapyDesc, tblPtThpy.ThpyStDtTm, tblPtThpy.ThpyEndDtTm,
IIf([ThpyStDtTm]<[Enter Start Date],[Enter Start Date],[ThpyStDtTm]) AS
CalcStartDate, Nz([ThpyEndDtTm],[Enter End Date]) AS CalcEndDate,
Nz([ThpyEndDtTm],[Enter End Date])-IIf([ThpyStDtTm]<[Enter Start
Date],[Enter Start Date],[ThpyStDtTm]) AS VentLOSDays, [VentLOSDays]*24 AS
VentLOSHours
FROM (tblPatients INNER JOIN (tblAreaList INNER JOIN tblPtLocation ON
tblAreaList.AreaID=tblPtLocation.AreaID_fk) ON
tblPatients.PtID=tblPtLocation.PtID_fk) INNER JOIN (tblTherapyType INNER
JOIN tblPtThpy ON tblTherapyType.TherapyTypeID=tblPtThpy.ThpyTypeID_fk) ON
tblPatients.PtID=tblPtThpy.PtID_fk
GROUP BY tblPatients.PtLName, tblPatients.PtID, tblPatients.AdmitNum,
tblPtThpy.PtThpyID, tblAreaList.AreaName, tblTherapyType.TherapyTypeID,
tblTherapyType.TherapyDesc, tblPtThpy.ThpyStDtTm, tblPtThpy.ThpyEndDtTm
HAVING (((tblAreaList.AreaName)="picu") AND
((tblTherapyType.TherapyTypeID)=70 Or (tblTherapyType.TherapyTypeID)=81) AND
((Nz([ThpyEndDtTm],[Enter End Date])) Between [Enter Start Date] And [Enter
End Date]))
ORDER BY tblPatients.PtLName;
Click to expand...
 
L

Linda RQ

I do need the time because we need to know how many hours the patient was
actually on the therapy. I am hoping that in my calculated fields, just
having the date will assume the time is midnight? I don't know what you
mean by aggregate value...I'll go look it up

Linda


Duane Hookom said:
I'm not sure why this is a totals query when there are no aggregated
values.
You might want convert your Date+Time values to just the Date with
DateValue([YourDateField])

PARAMETERS [Enter Start Date] DateTime, [Enter End Date] DateTime;
SELECT tblPatients.PtLName, tblPatients.PtID, tblPatients.AdmitNum,
tblPtThpy.PtThpyID, tblAreaList.AreaName, tblTherapyType.TherapyTypeID,
tblTherapyType.TherapyDesc, tblPtThpy.ThpyStDtTm, tblPtThpy.ThpyEndDtTm,
IIf([ThpyStDtTm]<[Enter Start Date],[Enter Start Date],[ThpyStDtTm]) AS
CalcStartDate, Nz([ThpyEndDtTm],[Enter End Date]) AS CalcEndDate,
Nz([ThpyEndDtTm],[Enter End Date])-IIf([ThpyStDtTm]<[Enter Start
Date],[Enter Start Date],[ThpyStDtTm]) AS VentLOSDays, [VentLOSDays]*24 AS
VentLOSHours
FROM (tblPatients INNER JOIN (tblAreaList INNER JOIN tblPtLocation ON
tblAreaList.AreaID=tblPtLocation.AreaID_fk) ON
tblPatients.PtID=tblPtLocation.PtID_fk) INNER JOIN (tblTherapyType INNER
JOIN tblPtThpy ON tblTherapyType.TherapyTypeID=tblPtThpy.ThpyTypeID_fk) ON
tblPatients.PtID=tblPtThpy.PtID_fk
WHERE tblAreaList.AreaName="picu" AND
tblTherapyType.TherapyTypeID IN (70,81) AND
Datevalue(Nz([ThpyEndDtTm],[Enter End Date])) Between [Enter Start Date]
And
[Enter
End Date];
--
Duane Hookom
Microsoft Access MVP


Linda RQ said:
Hi Everyone,

Access 2003. I use the query grid to create my query but pasted the sql
below so if the answer can be directed so I can use the grid, that would
help.

I need my query to display a list of patients who were on a therapy 70 or
81
in the picu. It is working great except, I entered these dates...Start
Date
7/1/07, End Date 9/30/07. The problem is, it is not showing 2 records
who
have end dates of 9/30/07. If I type in Start Date 7/1/07 and End Date
10/1/07 they are included on that. It wouldn't make sense to the user to
type in 10/1/07 because they are looking for dates between 7/1 and 9/30.

Thanks,
Linda

PARAMETERS [Enter Start Date] DateTime, [Enter End Date] DateTime;
SELECT tblPatients.PtLName, tblPatients.PtID, tblPatients.AdmitNum,
tblPtThpy.PtThpyID, tblAreaList.AreaName, tblTherapyType.TherapyTypeID,
tblTherapyType.TherapyDesc, tblPtThpy.ThpyStDtTm, tblPtThpy.ThpyEndDtTm,
IIf([ThpyStDtTm]<[Enter Start Date],[Enter Start Date],[ThpyStDtTm]) AS
CalcStartDate, Nz([ThpyEndDtTm],[Enter End Date]) AS CalcEndDate,
Nz([ThpyEndDtTm],[Enter End Date])-IIf([ThpyStDtTm]<[Enter Start
Date],[Enter Start Date],[ThpyStDtTm]) AS VentLOSDays, [VentLOSDays]*24
AS
VentLOSHours
FROM (tblPatients INNER JOIN (tblAreaList INNER JOIN tblPtLocation ON
tblAreaList.AreaID=tblPtLocation.AreaID_fk) ON
tblPatients.PtID=tblPtLocation.PtID_fk) INNER JOIN (tblTherapyType INNER
JOIN tblPtThpy ON tblTherapyType.TherapyTypeID=tblPtThpy.ThpyTypeID_fk)
ON
tblPatients.PtID=tblPtThpy.PtID_fk
GROUP BY tblPatients.PtLName, tblPatients.PtID, tblPatients.AdmitNum,
tblPtThpy.PtThpyID, tblAreaList.AreaName, tblTherapyType.TherapyTypeID,
tblTherapyType.TherapyDesc, tblPtThpy.ThpyStDtTm, tblPtThpy.ThpyEndDtTm
HAVING (((tblAreaList.AreaName)="picu") AND
((tblTherapyType.TherapyTypeID)=70 Or (tblTherapyType.TherapyTypeID)=81)
AND
((Nz([ThpyEndDtTm],[Enter End Date])) Between [Enter Start Date] And
[Enter
End Date]))
ORDER BY tblPatients.PtLName;
Click to expand...
Click to expand...
 
L

Linda RQ

OK...maybe I see what you are talking about. I do have the sum row open
because I needed to change the calcDate fields to expressions instead of
group by. Should I always remember to collapse that if I am not trying to
sum something?

Thanks,
Linda

Duane Hookom said:
I'm not sure why this is a totals query when there are no aggregated
values.
You might want convert your Date+Time values to just the Date with
DateValue([YourDateField])

PARAMETERS [Enter Start Date] DateTime, [Enter End Date] DateTime;
SELECT tblPatients.PtLName, tblPatients.PtID, tblPatients.AdmitNum,
tblPtThpy.PtThpyID, tblAreaList.AreaName, tblTherapyType.TherapyTypeID,
tblTherapyType.TherapyDesc, tblPtThpy.ThpyStDtTm, tblPtThpy.ThpyEndDtTm,
IIf([ThpyStDtTm]<[Enter Start Date],[Enter Start Date],[ThpyStDtTm]) AS
CalcStartDate, Nz([ThpyEndDtTm],[Enter End Date]) AS CalcEndDate,
Nz([ThpyEndDtTm],[Enter End Date])-IIf([ThpyStDtTm]<[Enter Start
Date],[Enter Start Date],[ThpyStDtTm]) AS VentLOSDays, [VentLOSDays]*24 AS
VentLOSHours
FROM (tblPatients INNER JOIN (tblAreaList INNER JOIN tblPtLocation ON
tblAreaList.AreaID=tblPtLocation.AreaID_fk) ON
tblPatients.PtID=tblPtLocation.PtID_fk) INNER JOIN (tblTherapyType INNER
JOIN tblPtThpy ON tblTherapyType.TherapyTypeID=tblPtThpy.ThpyTypeID_fk) ON
tblPatients.PtID=tblPtThpy.PtID_fk
WHERE tblAreaList.AreaName="picu" AND
tblTherapyType.TherapyTypeID IN (70,81) AND
Datevalue(Nz([ThpyEndDtTm],[Enter End Date])) Between [Enter Start Date]
And
[Enter
End Date];
--
Duane Hookom
Microsoft Access MVP


Linda RQ said:
Hi Everyone,

Access 2003. I use the query grid to create my query but pasted the sql
below so if the answer can be directed so I can use the grid, that would
help.

I need my query to display a list of patients who were on a therapy 70 or
81
in the picu. It is working great except, I entered these dates...Start
Date
7/1/07, End Date 9/30/07. The problem is, it is not showing 2 records
who
have end dates of 9/30/07. If I type in Start Date 7/1/07 and End Date
10/1/07 they are included on that. It wouldn't make sense to the user to
type in 10/1/07 because they are looking for dates between 7/1 and 9/30.

Thanks,
Linda

PARAMETERS [Enter Start Date] DateTime, [Enter End Date] DateTime;
SELECT tblPatients.PtLName, tblPatients.PtID, tblPatients.AdmitNum,
tblPtThpy.PtThpyID, tblAreaList.AreaName, tblTherapyType.TherapyTypeID,
tblTherapyType.TherapyDesc, tblPtThpy.ThpyStDtTm, tblPtThpy.ThpyEndDtTm,
IIf([ThpyStDtTm]<[Enter Start Date],[Enter Start Date],[ThpyStDtTm]) AS
CalcStartDate, Nz([ThpyEndDtTm],[Enter End Date]) AS CalcEndDate,
Nz([ThpyEndDtTm],[Enter End Date])-IIf([ThpyStDtTm]<[Enter Start
Date],[Enter Start Date],[ThpyStDtTm]) AS VentLOSDays, [VentLOSDays]*24
AS
VentLOSHours
FROM (tblPatients INNER JOIN (tblAreaList INNER JOIN tblPtLocation ON
tblAreaList.AreaID=tblPtLocation.AreaID_fk) ON
tblPatients.PtID=tblPtLocation.PtID_fk) INNER JOIN (tblTherapyType INNER
JOIN tblPtThpy ON tblTherapyType.TherapyTypeID=tblPtThpy.ThpyTypeID_fk)
ON
tblPatients.PtID=tblPtThpy.PtID_fk
GROUP BY tblPatients.PtLName, tblPatients.PtID, tblPatients.AdmitNum,
tblPtThpy.PtThpyID, tblAreaList.AreaName, tblTherapyType.TherapyTypeID,
tblTherapyType.TherapyDesc, tblPtThpy.ThpyStDtTm, tblPtThpy.ThpyEndDtTm
HAVING (((tblAreaList.AreaName)="picu") AND
((tblTherapyType.TherapyTypeID)=70 Or (tblTherapyType.TherapyTypeID)=81)
AND
((Nz([ThpyEndDtTm],[Enter End Date])) Between [Enter Start Date] And
[Enter
End Date]))
ORDER BY tblPatients.PtLName;
Click to expand...
Click to expand...
 
G

Guest

"Aggregate" means that you are combining multiple records to display a count
or sum or average or whatever.

My suggested SQL only truncates the Time within the criteria. It will have
no effect on the calculations.

Did you try my sql?

--
Duane Hookom
Microsoft Access MVP


Linda RQ said:
OK...maybe I see what you are talking about. I do have the sum row open
because I needed to change the calcDate fields to expressions instead of
group by. Should I always remember to collapse that if I am not trying to
sum something?

Thanks,
Linda

Duane Hookom said:
I'm not sure why this is a totals query when there are no aggregated
values.
You might want convert your Date+Time values to just the Date with
DateValue([YourDateField])

PARAMETERS [Enter Start Date] DateTime, [Enter End Date] DateTime;
SELECT tblPatients.PtLName, tblPatients.PtID, tblPatients.AdmitNum,
tblPtThpy.PtThpyID, tblAreaList.AreaName, tblTherapyType.TherapyTypeID,
tblTherapyType.TherapyDesc, tblPtThpy.ThpyStDtTm, tblPtThpy.ThpyEndDtTm,
IIf([ThpyStDtTm]<[Enter Start Date],[Enter Start Date],[ThpyStDtTm]) AS
CalcStartDate, Nz([ThpyEndDtTm],[Enter End Date]) AS CalcEndDate,
Nz([ThpyEndDtTm],[Enter End Date])-IIf([ThpyStDtTm]<[Enter Start
Date],[Enter Start Date],[ThpyStDtTm]) AS VentLOSDays, [VentLOSDays]*24 AS
VentLOSHours
FROM (tblPatients INNER JOIN (tblAreaList INNER JOIN tblPtLocation ON
tblAreaList.AreaID=tblPtLocation.AreaID_fk) ON
tblPatients.PtID=tblPtLocation.PtID_fk) INNER JOIN (tblTherapyType INNER
JOIN tblPtThpy ON tblTherapyType.TherapyTypeID=tblPtThpy.ThpyTypeID_fk) ON
tblPatients.PtID=tblPtThpy.PtID_fk
WHERE tblAreaList.AreaName="picu" AND
tblTherapyType.TherapyTypeID IN (70,81) AND
Datevalue(Nz([ThpyEndDtTm],[Enter End Date])) Between [Enter Start Date]
And
[Enter
End Date];
--
Duane Hookom
Microsoft Access MVP


Linda RQ said:
Hi Everyone,

Access 2003. I use the query grid to create my query but pasted the sql
below so if the answer can be directed so I can use the grid, that would
help.

I need my query to display a list of patients who were on a therapy 70 or
81
in the picu. It is working great except, I entered these dates...Start
Date
7/1/07, End Date 9/30/07. The problem is, it is not showing 2 records
who
have end dates of 9/30/07. If I type in Start Date 7/1/07 and End Date
10/1/07 they are included on that. It wouldn't make sense to the user to
type in 10/1/07 because they are looking for dates between 7/1 and 9/30.

Thanks,
Linda

PARAMETERS [Enter Start Date] DateTime, [Enter End Date] DateTime;
SELECT tblPatients.PtLName, tblPatients.PtID, tblPatients.AdmitNum,
tblPtThpy.PtThpyID, tblAreaList.AreaName, tblTherapyType.TherapyTypeID,
tblTherapyType.TherapyDesc, tblPtThpy.ThpyStDtTm, tblPtThpy.ThpyEndDtTm,
IIf([ThpyStDtTm]<[Enter Start Date],[Enter Start Date],[ThpyStDtTm]) AS
CalcStartDate, Nz([ThpyEndDtTm],[Enter End Date]) AS CalcEndDate,
Nz([ThpyEndDtTm],[Enter End Date])-IIf([ThpyStDtTm]<[Enter Start
Date],[Enter Start Date],[ThpyStDtTm]) AS VentLOSDays, [VentLOSDays]*24
AS
VentLOSHours
FROM (tblPatients INNER JOIN (tblAreaList INNER JOIN tblPtLocation ON
tblAreaList.AreaID=tblPtLocation.AreaID_fk) ON
tblPatients.PtID=tblPtLocation.PtID_fk) INNER JOIN (tblTherapyType INNER
JOIN tblPtThpy ON tblTherapyType.TherapyTypeID=tblPtThpy.ThpyTypeID_fk)
ON
tblPatients.PtID=tblPtThpy.PtID_fk
GROUP BY tblPatients.PtLName, tblPatients.PtID, tblPatients.AdmitNum,
tblPtThpy.PtThpyID, tblAreaList.AreaName, tblTherapyType.TherapyTypeID,
tblTherapyType.TherapyDesc, tblPtThpy.ThpyStDtTm, tblPtThpy.ThpyEndDtTm
HAVING (((tblAreaList.AreaName)="picu") AND
((tblTherapyType.TherapyTypeID)=70 Or (tblTherapyType.TherapyTypeID)=81)
AND
((Nz([ThpyEndDtTm],[Enter End Date])) Between [Enter Start Date] And
[Enter
End Date]))
ORDER BY tblPatients.PtLName;
Click to expand...
Click to expand...
Click to expand...
 
L

Linda RQ

Thanks...Oh...your sql? (I'm pointing to my head and saying "blond" <g>)
I'll give it a try and let you know probably tomorrow.

Linda

Duane Hookom said:
"Aggregate" means that you are combining multiple records to display a
count
or sum or average or whatever.

My suggested SQL only truncates the Time within the criteria. It will have
no effect on the calculations.

Did you try my sql?

--
Duane Hookom
Microsoft Access MVP


Linda RQ said:
OK...maybe I see what you are talking about. I do have the sum row open
because I needed to change the calcDate fields to expressions instead of
group by. Should I always remember to collapse that if I am not trying
to
sum something?

Thanks,
Linda

Duane Hookom said:
I'm not sure why this is a totals query when there are no aggregated
values.
You might want convert your Date+Time values to just the Date with
DateValue([YourDateField])

PARAMETERS [Enter Start Date] DateTime, [Enter End Date] DateTime;
SELECT tblPatients.PtLName, tblPatients.PtID, tblPatients.AdmitNum,
tblPtThpy.PtThpyID, tblAreaList.AreaName, tblTherapyType.TherapyTypeID,
tblTherapyType.TherapyDesc, tblPtThpy.ThpyStDtTm,
tblPtThpy.ThpyEndDtTm,
IIf([ThpyStDtTm]<[Enter Start Date],[Enter Start Date],[ThpyStDtTm]) AS
CalcStartDate, Nz([ThpyEndDtTm],[Enter End Date]) AS CalcEndDate,
Nz([ThpyEndDtTm],[Enter End Date])-IIf([ThpyStDtTm]<[Enter Start
Date],[Enter Start Date],[ThpyStDtTm]) AS VentLOSDays, [VentLOSDays]*24
AS
VentLOSHours
FROM (tblPatients INNER JOIN (tblAreaList INNER JOIN tblPtLocation ON
tblAreaList.AreaID=tblPtLocation.AreaID_fk) ON
tblPatients.PtID=tblPtLocation.PtID_fk) INNER JOIN (tblTherapyType
INNER
JOIN tblPtThpy ON tblTherapyType.TherapyTypeID=tblPtThpy.ThpyTypeID_fk)
ON
tblPatients.PtID=tblPtThpy.PtID_fk
WHERE tblAreaList.AreaName="picu" AND
tblTherapyType.TherapyTypeID IN (70,81) AND
Datevalue(Nz([ThpyEndDtTm],[Enter End Date])) Between [Enter Start
Date]
And
[Enter
End Date];
--
Duane Hookom
Microsoft Access MVP


:

Hi Everyone,

Access 2003. I use the query grid to create my query but pasted the
sql
below so if the answer can be directed so I can use the grid, that
would
help.

I need my query to display a list of patients who were on a therapy 70
or
81
in the picu. It is working great except, I entered these
dates...Start
Date
7/1/07, End Date 9/30/07. The problem is, it is not showing 2 records
who
have end dates of 9/30/07. If I type in Start Date 7/1/07 and End
Date
10/1/07 they are included on that. It wouldn't make sense to the user
to
type in 10/1/07 because they are looking for dates between 7/1 and
9/30.

Thanks,
Linda

PARAMETERS [Enter Start Date] DateTime, [Enter End Date] DateTime;
SELECT tblPatients.PtLName, tblPatients.PtID, tblPatients.AdmitNum,
tblPtThpy.PtThpyID, tblAreaList.AreaName,
tblTherapyType.TherapyTypeID,
tblTherapyType.TherapyDesc, tblPtThpy.ThpyStDtTm,
tblPtThpy.ThpyEndDtTm,
IIf([ThpyStDtTm]<[Enter Start Date],[Enter Start Date],[ThpyStDtTm])
AS
CalcStartDate, Nz([ThpyEndDtTm],[Enter End Date]) AS CalcEndDate,
Nz([ThpyEndDtTm],[Enter End Date])-IIf([ThpyStDtTm]<[Enter Start
Date],[Enter Start Date],[ThpyStDtTm]) AS VentLOSDays,
[VentLOSDays]*24
AS
VentLOSHours
FROM (tblPatients INNER JOIN (tblAreaList INNER JOIN tblPtLocation ON
tblAreaList.AreaID=tblPtLocation.AreaID_fk) ON
tblPatients.PtID=tblPtLocation.PtID_fk) INNER JOIN (tblTherapyType
INNER
JOIN tblPtThpy ON
tblTherapyType.TherapyTypeID=tblPtThpy.ThpyTypeID_fk)
ON
tblPatients.PtID=tblPtThpy.PtID_fk
GROUP BY tblPatients.PtLName, tblPatients.PtID, tblPatients.AdmitNum,
tblPtThpy.PtThpyID, tblAreaList.AreaName,
tblTherapyType.TherapyTypeID,
tblTherapyType.TherapyDesc, tblPtThpy.ThpyStDtTm,
tblPtThpy.ThpyEndDtTm
HAVING (((tblAreaList.AreaName)="picu") AND
((tblTherapyType.TherapyTypeID)=70 Or
(tblTherapyType.TherapyTypeID)=81)
AND
((Nz([ThpyEndDtTm],[Enter End Date])) Between [Enter Start Date] And
[Enter
End Date]))
ORDER BY tblPatients.PtLName;
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Need query in hours not days 2
Parameter query with expressions and calculations 3
DateAdd Function 9
No records with date criteria 7
Crosstab showing too many rows 4
Using Between Dates in a Query 4
Unmatched Query based between two dates 0
troubleshooting a Between ... And ... query with dates 1

Top