The report is based on a query but the reporting period parameters are determined in a parameters form that provides a WhereCondition SQL statement when opening the report.
In composing this question I realized the problem: the query is selecting Service Dates, so anyone who didn't receive services during the reporting period is not in the results to begin with. I tried creating a join that would show all patients and only the events with matching IDs in the foreign field, but got an error that it required separate queries.
I think I need a whole new query, but I can't think of how to construct it. All the data I need is in 3 tables: Patients; Volunteers; and ServiceEvents. Patients & Volunteers are both 1 to many with ServiceEvents. The core problem seems to be that ServiceEvents does not include anyone who didn't receive services during a specified period.
Here are the SQL statements I've tried.
The original one (which worked fine) :
Code: Select all
rptWhere = "SvcMonth Between " & dStart & " And " & dEnd
Code: Select all
rptWhere = "(SvcMonth Between " & dStart & " And " & dEnd _
& ") Or ((CloseMonth Between " & dStart & " And " & dEnd _
& ") And Reason = 'Died'))"
Code: Select all
(SvcMonth Between 1912 And 1912) Or ((CloseMonth Between 1912 And 1912) And Reason = “Died”)