M
MS_Access_Neophyte
Thanks to this and other forums, and the access MVPs out there (especially
John Spencer), I'm so close to getting the data I need. Here's the SQL view
of the part that almost works:
*******************
SELECT Count([BMC Client Data Table].BMC_ID) AS CountOfBMC_ID, tDates2.fDate
FROM [BMC Client Data Table], tDates2
WHERE (((tDates2.fDate) Between [BMC Client Data Table]![AdmitDate] And [BMC
Client Data Table]![DC_DischargeDate]))
GROUP BY tDates2.fDate
HAVING (((tDates2.fDate) Between [Starting Date] And (DateAdd("d",-1,[Ending
Date]))));
*******************
Problem is, this returns only the number of clients in program on all the
days, whose “DC_DischargeDate†is not null. I need the expression to
include any clients who have not discharged yet - whose “DC_DischargeDate†is
Null.
I tried changing the expression to this, but it returned nothing – not even
the previous result. Where am I going wrong?
*******************
IIf([DC_DischargeDate] Is Null,([tDates2].[fDate]) Between [BMC Client Data
Table]![AdmitDate] And (Date()),([tDates2].[fDate]) Between [BMC Client Data
Table]![AdmitDate] And [BMC Client Data Table]![DC_DischargeDate])
*******************
Thanks so much!!
Access Neophyte
John Spencer), I'm so close to getting the data I need. Here's the SQL view
of the part that almost works:
*******************
SELECT Count([BMC Client Data Table].BMC_ID) AS CountOfBMC_ID, tDates2.fDate
FROM [BMC Client Data Table], tDates2
WHERE (((tDates2.fDate) Between [BMC Client Data Table]![AdmitDate] And [BMC
Client Data Table]![DC_DischargeDate]))
GROUP BY tDates2.fDate
HAVING (((tDates2.fDate) Between [Starting Date] And (DateAdd("d",-1,[Ending
Date]))));
*******************
Problem is, this returns only the number of clients in program on all the
days, whose “DC_DischargeDate†is not null. I need the expression to
include any clients who have not discharged yet - whose “DC_DischargeDate†is
Null.
I tried changing the expression to this, but it returned nothing – not even
the previous result. Where am I going wrong?
*******************
IIf([DC_DischargeDate] Is Null,([tDates2].[fDate]) Between [BMC Client Data
Table]![AdmitDate] And (Date()),([tDates2].[fDate]) Between [BMC Client Data
Table]![AdmitDate] And [BMC Client Data Table]![DC_DischargeDate])
*******************
Thanks so much!!
Access Neophyte