I am a novice at SQL and am learning as I am going. Used below query to obtain the number of chargeable days visitors spent in various facilities during October. This query provides this information if no new visits for November have been entered in the database when the query is run. I need help with 'DateDiff('d',IIf([ADATE]>#11/1/2010#...'
When run recently it included the new November visit days in the query result, showing these days as negative days and subtracting them from the October total.
Also, since we want to run this query every month, how can I set it up to enable the query period to be manually entered, e.g. Between[Enter Start Date]...
Thanks for your help.
SELECT tblDemo.HRN, tblDemo.LastName, tblDemo.FirstName, tblHistory.ADATE, tblFACILITYTABLE.[Facility Name], tblHistory.DDATE, DateDiff('d',IIf([ADATE]<#10/1/2010#,#10/1/2010#,[ADATE]),IIf(Nz([DDATE],Date())>#10/31/2010#,#11/1/2010#,Nz([DDATE],Date()))) AS VisitDays
FROM tblFACILITYTABLE INNER JOIN (tblDemo INNER JOIN tblHistory ON tblDemo.NUM = tblHistory.NUM) ON tblFACILITYTABLE.LOC = tblHistory.LOC
GROUP BY tblDemo.NUM, tblDemo.LastName, tblDemo.FirstName, tblHistory.ADATE, tblFACILITYTABLE.[Facility Name], tblHistory.DDATE, DateDiff('d',IIf([ADATE]<#10/1/2010#,#10/1/2010#,[ADATE]),IIf(Nz([DDATE],Date())>#10/31/2010#,#11/1/2010#,Nz([DDATE],Date())))
HAVING (((tblHistory.ADATE)<=#10/31/2010#) AND ((tblHistory.DDATE)>=#10/1/2010#)) OR (((Nz([DDate],Date()))>=#10/1/2010#));
camgar