Trouble adding a specific query parameter

Jeff H
4StarLounger
Posts: 415
Joined: 31 Oct 2017, 20:07

Trouble adding a specific query parameter

Post by Jeff H »

I have a query used to detail services provided during a specified period. So far it works fine because it just finds all the service dates within the period. But I need to add people who died within the reporting period without receiving services.

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
The revised one:

Code: Select all

rptWhere = "(SvcMonth Between " & dStart & " And " & dEnd _
    & ") Or ((CloseMonth Between " & dStart & " And " & dEnd _
    & ") And Reason = 'Died'))"
Which was based on my thinking that this would be a valid SQL statement for this purpose:

Code: Select all

(SvcMonth Between 1912 And 1912) Or ((CloseMonth Between 1912 And 1912) And Reason = “Died”)
NOTE: SvcMonth and CloseMonth both take the relevant date (Service or Closed) and format it as "yymm". So the example here is specifying December 2019.

User avatar
HansV
Administrator
Posts: 78232
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Trouble adding a specific query parameter

Post by HansV »

Is CloseMonth a number field or a text field?
Best wishes,
Hans

Jeff H
4StarLounger
Posts: 415
Joined: 31 Oct 2017, 20:07

Re: Trouble adding a specific query parameter

Post by Jeff H »

That's a good question. The EOLSClosed field is a date. I get CloseMonth in the query with CloseMonth:Format(EOLSClosed,"yymm"). Looks and sorts like a number but could be a string I guess.

User avatar
HansV
Administrator
Posts: 78232
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Trouble adding a specific query parameter

Post by HansV »

And what are dStart and dEnd? Date variables? Or string variables?
Best wishes,
Hans

Jeff H
4StarLounger
Posts: 415
Joined: 31 Oct 2017, 20:07

Re: Trouble adding a specific query parameter

Post by Jeff H »

They are Long variables dim'd in the Ok click event.

User avatar
HansV
Administrator
Posts: 78232
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Trouble adding a specific query parameter

Post by HansV »

Thanks. I think it should be:

Code: Select all

rptWhere = "SvcMonth Between " & dStart & " And " & dEnd _
    & " Or CloseMonth Between '" & dStart & "' And '" & dEnd _
    & "' And Reason = 'Died'"
Best wishes,
Hans

Jeff H
4StarLounger
Posts: 415
Joined: 31 Oct 2017, 20:07

Re: Trouble adding a specific query parameter

Post by Jeff H »

I just tried it and got the same results. SvcMonth is derived the same way as CloseMonth, and dStart/dEnd are both declared as Long in the same procedure.

I think the problem is that the query is primarily looking at records in the ServiceEvents table, then the Patients and Volunteers tables add detail (like names). People who haven't received services within the reporting period are not included, so the query can't find CloseMonth and Reason data for them.

User avatar
HansV
Administrator
Posts: 78232
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Trouble adding a specific query parameter

Post by HansV »

Could you send me a copy of the database again?
Best wishes,
Hans

Jeff H
4StarLounger
Posts: 415
Joined: 31 Oct 2017, 20:07

Re: Trouble adding a specific query parameter

Post by Jeff H »

Ok.

User avatar
HansV
Administrator
Posts: 78232
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Trouble adding a specific query parameter

Post by HansV »

In the first place, you'll have to change the joins in qryRptMonthly to outer joins so that the query returns all patients, even those without service events.

S3484.png

In the second place, the where-condition will have to be changed. Unfortunately, I can get it to work if I place the where-condition directly in the query, but not in DoCmd.OpenReport, and it's after midnight here. I'll try again tomorrow. Sorry!
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

Jeff H
4StarLounger
Posts: 415
Joined: 31 Oct 2017, 20:07

Re: Trouble adding a specific query parameter

Post by Jeff H »

Anxiously awaiting that SQL statement. Earlier, when I changed the joins, I got an error. I tried changing just the Patient/Event join then also changed the Volunteer/Event join, but got the same error both times. I don't know how to construct a SQL that will fix it.
Outer Join Error.png
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78232
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Trouble adding a specific query parameter

Post by HansV »

In the end, the problem was somewhere else than I thought. The SvcMonthLabel text box on the report won't work if SvcMonth is null.
I will send you the modified database.
PS I changed CloseMonth to a number instead of a string, and I changed SvcMonthLabel to a date, with the format applied in the text box on the report.
Best wishes,
Hans

Jeff H
4StarLounger
Posts: 415
Joined: 31 Oct 2017, 20:07

Re: Trouble adding a specific query parameter

Post by Jeff H »

At first look this looks really good, Hans. In order to fully test it, I'm not sure how to apply your changes to the actual db. I had written a procedure to remove all identifying info, which just requires an update query on three tables. In the past I've been able to find your changes and manually update the db, but I'm not confident I can pinpoint what you did this time.

I'm going to try removing the patient, volunteer, and contact info tables from a copy of your db then importing the real tables. Alternatively I could import the real tables then run update queries in reverse to restore the names and such. Would one method be preferable to the other (or are they both risky)?

User avatar
HansV
Administrator
Posts: 78232
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Trouble adding a specific query parameter

Post by HansV »

The only objects that I changed are:
qryReportMonthly
frmReportParameters
rptMonthly

So you could delete those three from your own database, then import them from my version.
I didn't change the design of the tables.
Best wishes,
Hans

Jeff H
4StarLounger
Posts: 415
Joined: 31 Oct 2017, 20:07

Re: Trouble adding a specific query parameter

Post by Jeff H »

Thanks. That helps me see what you did. I already use the import and update method to restore the names in the db you returned and it worked fine. I'm going to check it all out now.

Jeff H
4StarLounger
Posts: 415
Joined: 31 Oct 2017, 20:07

Re: Trouble adding a specific query parameter

Post by Jeff H »

I checked a monthly report against a query of those who died without services in that month and everyone was included.

Now I'm going to examine how you did that. I also need to figure out how to clean up the aggregates row for those without services, but I think that shouldn't be hard.

As always, thank very much Hans!

- Jeff