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, rptDataAnalystP roductivity, with qryAuditorsTota lReview as the record source, and I've created my unbound form, frmReportDataAn alystProductivi ty. 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 qryAuditorRevie w (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 qryAuditorsTota lReview, and qryAuditorsTota lReview is dependent on qryAuditorsRevi ew 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 - qryAuditorsRevi ew
[code=sql]
SELECT qryAuditorsActi ve.AuditorFullN ame, qryPatientDetai l.PatientMPI, qryPatientDetai l.Episode, qryPatientDetai l.DischargeDate , qryCampus.Campu s, qryPatientDetai l.DatePIAReceiv ed, qryPatientDetai l.DateAudited, DateDiff("d",[DischargeDate],[DateAudited]) AS DaysSinceDischa rge, DateDiff("d",[DischargeDate],[DatePIAReceived]) AS DaysToReceivePI A, DateDiff("d",[DatePIAReceived],[DateAudited]) AS DaysToAudit, DateDiff("d",[DatePiaReceived],[30Day]) AS Received30Day, DateAdd("d",30,[DischargeDate]) AS 30Day, IIf(([DaysToReceivePI A]>30),0,IIf(([DaysToReceivePI A]<=30) And (([DaysToReceivePI A]+[DaysToAudit])<=30),0,1)) AS NotAuditedTimel y
FROM (qryPatientDeta il INNER JOIN qryAuditorsActi ve ON qryPatientDetai l.AuditorIDFK = qryAuditorsActi ve.AuditorID) INNER JOIN qryCampus ON qryAuditorsActi ve.CampusIDFK = qryCampus.Campu sID
WHERE (((qryPatientDe tail.DischargeD ate) Between [Forms]![frmReportDataAn alystProductivi ty]![BeginningDate] And [Forms]![frmReportDataAn alystProductivi ty]![EndingDate]) AND ((qryCampus.Cam pus)=[Forms]![frmReportDataAn alystProductivi ty]![Campus]));
[/code]

Query 2 - qryAuditorsTota lReview
[code=sql]
SELECT qryAuditorsRevi ew.AuditorFullN ame, qryAuditorsRevi ew.Campus, Count(qryAudito rsReview.Patien tMPI) AS TotalCharts, Sum(qryAuditors Review.NotAudit edTimely) AS NotAuditedTimel y
FROM qryAuditorsRevi ew
GROUP BY qryAuditorsRevi ew.AuditorFullN ame, qryAuditorsRevi ew.Campus;
[/code]

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]

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.

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 :)