By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,587 Members | 1,070 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,587 IT Pros & Developers. It's quick & easy.

Custom parameter dialog box for query/report

beacon
100+
P: 579
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:
Expand|Select|Wrap|Line Numbers
  1. Query 1 - qryAuditorsReview (This query is used to compile data)
  2.  
  3. Field: AuditorFullName
  4. Field: Campus
  5. Field: PatientMPI
  6. Field: DischargeDate
  7. Field: DatePIAReceived
  8. Field: DateAudited
  9. Expression: NotAuditedTimely (This expression returns a 1 or 0 for timeliness, and is based on the DischargeDate, DatePIAReceived, and DateAudited fields)
  10.  
Expand|Select|Wrap|Line Numbers
  1. Query 2 - qryAuditorsTotalReview (This query is used to summarize the data in qryAuditorsReview)
  2.  
  3. Field: AuditorFullName
  4. Field: Campus
  5. Field: TotalCharts (Count of PatientMPI)
  6. Field: NotAuditedTimely (Sum of NotAuditedTimely)
  7.  
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?
Jan 28 '10 #1

✓ answered by TheSmileyCoder

Expand|Select|Wrap|Line Numbers
  1. ((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:
Expand|Select|Wrap|Line Numbers
  1. ((qryCampus.CampusID)=[Forms]![frmReportDataAnalystProductivity]![Campus]))
Or
Expand|Select|Wrap|Line Numbers
  1. ((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.

Share this Question
Share on Google+
6 Replies


TheSmileyCoder
Expert Mod 100+
P: 2,321
Whats the SQL syntax for the queries?
Jan 28 '10 #2

beacon
100+
P: 579
Query 1 - qryAuditorsReview
Expand|Select|Wrap|Line Numbers
  1. 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
  2. FROM (qryPatientDetail INNER JOIN qryAuditorsActive ON qryPatientDetail.AuditorIDFK = qryAuditorsActive.AuditorID) INNER JOIN qryCampus ON qryAuditorsActive.CampusIDFK = qryCampus.CampusID
  3. WHERE (((qryPatientDetail.DischargeDate) Between [Forms]![frmReportDataAnalystProductivity]![BeginningDate] And [Forms]![frmReportDataAnalystProductivity]![EndingDate]) AND ((qryCampus.Campus)=[Forms]![frmReportDataAnalystProductivity]![Campus]));
  4.  
Query 2 - qryAuditorsTotalReview
Expand|Select|Wrap|Line Numbers
  1. SELECT qryAuditorsReview.AuditorFullName, qryAuditorsReview.Campus, Count(qryAuditorsReview.PatientMPI) AS TotalCharts, Sum(qryAuditorsReview.NotAuditedTimely) AS NotAuditedTimely
  2. FROM qryAuditorsReview
  3. GROUP BY qryAuditorsReview.AuditorFullName, qryAuditorsReview.Campus;
  4.  
Thanks and let me know if you need me to provide anything else (VBA, Form screen shot, a half-eaten cookie, etc.)
Jan 28 '10 #3

TheSmileyCoder
Expert Mod 100+
P: 2,321
Expand|Select|Wrap|Line Numbers
  1. ((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:
Expand|Select|Wrap|Line Numbers
  1. ((qryCampus.CampusID)=[Forms]![frmReportDataAnalystProductivity]![Campus]))
Or
Expand|Select|Wrap|Line Numbers
  1. ((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.
Jan 29 '10 #4

beacon
100+
P: 579
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
Jan 29 '10 #5

NeoPa
Expert Mod 15k+
P: 31,485
@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).
Jan 30 '10 #6

TheSmileyCoder
Expert Mod 100+
P: 2,321
Guess I learned something too then :)
Jan 31 '10 #7

Post your reply

Sign in to post your reply or Sign up for a free account.