Parameter query with expressions and calculations

Hi Everyone,

I am using Access 2003.

I have been working on this query for a few weeks using my Access book,
examples from this group and google searches. I am pretty close to getting
what I want but I am getting frustrated and mixed up and I can't seem to
figure it out. I'm starting to get mixed up trying to explain this too so I
am going to post and let you guys help me organize my thoughts...geesh! In
regular words here is what I want.

My database is of patients and information about the therapy we are giving
them and how long they are on that therapy.

I want to calculate how many days we had patients in a certain unit on
ventilators (VentLOSDays). I get the list of patients and the records from
this query and then export it to excel where I tally up the total days for
all the patients as well as look up other info and add it to that sheet. My
query pulls up patients from the NBICU who were on a vent (thpy ID 70, 81 or
91). That part seems to be working well. I made some expressions that are
sort of working

CalcStartDate is...If the patient was on the vent before the start date that
I enter in my parameter query then the date I enter will be the
CalcStartDate at midnight in this fieldCalcEndDate is....If the patient was
on the vent after the end date that I enter than that date is the
CalcEndDate at midnight.

If the patient was started and ended on the vent between the dates I enter,
those dates and times are returned in the CalcStartDate and CalcEndDate
fields for
those patients.

I want my VentLOSDays to calculate the days on the vent from the
CalcStartDate and CalcEndDate

Below is my sql but I used the query grid, I just post this because it
appears you guys can read this better than my explainations of what I put
where in the query grid g Below that is a few examples of data returned
for a few records.

PARAMETERS [Enter Start Date] DateTime, [Enter End Date] DateTime;SELECT
tblPatients.PtLName, tblPatients.PtID, tblPatients.AdmitNum,
tblPtThpy.PtThpyID, tblAreaList.AreaID, tblAreaList.AreaName,
tblTherapyType.TherapyTypeID, tblTherapyType.TherapyDesc,
tblPtThpy.ThpyStDtTm, tblPtThpy.ThpyEndDtTm, IIf([ThpyStDtTm][Enter Start
Date]-1,[Enter Start Date],[ThpyStDtTm]) AS CalcStartDate,
IIf(Nz([ThpyEndDtTm],[Enter End Date]),IIf([ThpyEndDtTm]=[Enter End
Date]+1,[Enter End Date],[ThpyEndDtTm]),[ThpyEndDtTm]) AS CalcEndDate,
Nz([ThpyEndDtTm],[Enter End Date])-IIf([ThpyStDtTm][Enter Start
Date],[Enter Start Date],[ThpyStDtTm]) AS VentLOSDays, [VentLOSDays]*24 AS
VentLOSHoursFROM (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.AreaID, tblAreaList.AreaName, tblTherapyType.TherapyTypeID,
tblTherapyType.TherapyDesc, tblPtThpy.ThpyStDtTm, tblPtThpy.ThpyEndDtTm
HAVING (((tblAreaList.AreaID)=20) AND ((tblTherapyType.TherapyTypeID)=70 Or
(tblTherapyType.TherapyTypeID)=81 Or (tblTherapyType.TherapyTypeID)=97) AND
((tblPtThpy.ThpyStDtTm)=[Enter End Date]+1) AND
((IIf(Nz([ThpyEndDtTm],[Enter End Date]),IIf([ThpyEndDtTm]=[Enter End
Date]+1,[Enter End Date],[ThpyEndDtTm])[ThpyEndDtTm])) Between [Enter Start
Date] And [Enter End Date])) ORDER BY tblPatients.PtLName;


Returned Data for a few records

ThpyStDtTm 9/24/09 and ThpyEndDtTm 10/13/09 16:50
CalcStartDate is 10/1/09 0:00 CalcEnd Date is 10/13/09 16:50
VentLOSDays is 12.7 (This is correct)

ThpyStDtTm 9/24/09 3:54 and ThpyEndDtTm 11/7/09 14:40
CalcStartDate is 10/1/09 0:00 and my CalcEndDate is 10/31/09 0:00
VentLOSDays returns 37.61 (Incorrect) should be (31 days)

ThpyStDtTm 10/30/09 19:28 and ThpyEndDtTm 11/8/09.
CalcStartDate is 10/30/09 19:20 and CalcEndDate is 10/31/09 0:00
Vent LOS returns 8.63 (Incorrect) should be (1.2 days)

Thanks,
Linda