Custom parameter dialog box for query/report

[beacon]

Hi everybody,

[Access 2003]

I've created custom parameter dialog boxes before that will call a report, which calls the underlying query, and passes the parameters accordingly, but I'm having trouble with one that I'm currently working on because it's slightly different than the others.

Here are the query definitions:

Query 1 - qryAuditorsReview (This query is used to compile data)
 
Field: AuditorFullName
Field: Campus
Field: PatientMPI
Field: DischargeDate
Field: DatePIAReceived
Field: DateAudited
Expression: NotAuditedTimely (This expression returns a 1 or 0 for timeliness, and is based on the DischargeDate, DatePIAReceived, and DateAudited fields)
 

Query 2 - qryAuditorsTotalReview (This query is used to summarize the data in qryAuditorsReview)
 
Field: AuditorFullName
Field: Campus
Field: TotalCharts (Count of PatientMPI)
Field: NotAuditedTimely (Sum of NotAuditedTimely)
 

I'm created my report, rptDataAnalystProductivity, with qryAuditorsTotalReview as the record source, and I've created my unbound form, frmReportDataAnalystProductivity. The form has 3 fields: BeginningDate (textbox), EndingDate (textbox), and Campus (combo box with 2 values from control source 'qryCampus'). I've set up parameter criteria in qryAuditorReview (the 1st query) for each of the 3 fields on the form so that the query can retrieve the values and the report will run according to those parameters.

Basically, my report is dependent on qryAuditorsTotalReview, and qryAuditorsTotalReview is dependent on qryAuditorsReview to get the parameters from the form. The issue I'm running into is that the report will return no data if I run it using the form. I can run both queries and return data, and can run the report by itself and return data, but the form says that no data is returned.

I played around with the queries and found that if I remove Campus from both of the queries, that the form will run successfully. However, if I remove Campus from only one query (doesn't matter which one), the form won't run. It's like the form is passing the Campus parameter, which sucks because I really need Campus to work in order to filter the data properly.

Anyone have any ideas what I'm doing wrong?

[TheSmileyCoder]

Whats the SQL syntax for the queries?

[beacon]

Query 1 - qryAuditorsReview

SELECT qryAuditorsActive.AuditorFullName, qryPatientDetail.PatientMPI, qryPatientDetail.Episode, qryPatientDetail.DischargeDate, qryCampus.Campus, qryPatientDetail.DatePIAReceived, qryPatientDetail.DateAudited, DateDiff("d",[DischargeDate],[DateAudited]) AS DaysSinceDischarge, DateDiff("d",[DischargeDate],[DatePIAReceived]) AS DaysToReceivePIA, DateDiff("d",[DatePIAReceived],[DateAudited]) AS DaysToAudit, DateDiff("d",[DatePiaReceived],[30Day]) AS Received30Day, DateAdd("d",30,[DischargeDate]) AS 30Day, IIf(([DaysToReceivePIA]>30),0,IIf(([DaysToReceivePIA]<=30) And (([DaysToReceivePIA]+[DaysToAudit])<=30),0,1)) AS NotAuditedTimely
FROM (qryPatientDetail INNER JOIN qryAuditorsActive ON qryPatientDetail.AuditorIDFK = qryAuditorsActive.AuditorID) INNER JOIN qryCampus ON qryAuditorsActive.CampusIDFK = qryCampus.CampusID
WHERE (((qryPatientDetail.DischargeDate) Between [Forms]![frmReportDataAnalystProductivity]![BeginningDate] And [Forms]![frmReportDataAnalystProductivity]![EndingDate]) AND ((qryCampus.Campus)=[Forms]![frmReportDataAnalystProductivity]![Campus]));
 

Query 2 - qryAuditorsTotalReview

SELECT qryAuditorsReview.AuditorFullName, qryAuditorsReview.Campus, Count(qryAuditorsReview.PatientMPI) AS TotalCharts, Sum(qryAuditorsReview.NotAuditedTimely) AS NotAuditedTimely
FROM qryAuditorsReview
GROUP BY qryAuditorsReview.AuditorFullName, qryAuditorsReview.Campus;
 

Thanks and let me know if you need me to provide anything else (VBA, Form screen shot, a half-eaten cookie, etc.)

[TheSmileyCoder]

((qryCampus.Campus)=[Forms]![frmReportDataAnalystProductivity]![Campus]))

I think is the issue. Let me guess that your combobox on the form has a hidden first column (zero-width column) containing the CampusID. Even though the field is hidden from human view, the code will always take the first column, as that is the actual "value" stored in that field, the other is just displayed information.
Either do:

((qryCampus.CampusID)=[Forms]![frmReportDataAnalystProductivity]![Campus]))

Or

((qryCampus.Campus)=[Forms]![frmReportDataAnalystProductivity]![Campus].Column(1)))

First column is 0

Id suggest also naming the combobox something like cboCampus or cmbCampus, that makes you realise when doing queries like these that your dealing with a combobox, and might need to look at the stored value and not the displayed one.

Let me know if this was the problem, and if these hints solved it.

[beacon]

All I have to say is wow! Good guess...

I tried changing my query to match what you provided, but neither worked. However, it did get me thinking about the bound column that I was using for the combo box, which was the first column. I changed that to the second column and everything worked out perfect...can't believe I overlooked that when I was creating the form.

I usually do what you suggested, but didn't for this query/form/report because it's the first one I've written where I've had to use Campus for anything...but I will go back and change it to match up with my other fields when I go back in to document my VBA.

Thanks for your help Smiley!

~beacon

[NeoPa]

@TheSmileyOne
I would only comment on this particular point in an otherwise perfect and helpful answer.

The first column is the Bound Column by default, yet any column can be set this way by changing this property in Design View. The value will always reflect this column (Bound Column).

[TheSmileyCoder]

Guess I learned something too then :)