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

Using a Parameter Query in a Report

P: 1
I have written a report that lists the Patient Id, phone number &ct, for a specific Dr's office and a specific date.To execute the report and attendant query, I use a form to get the Date and Office Name. I then set me.application.tempvars('1") and me.application.tempvars("2") to the Date and Office of interest. In the Open_Report subroutine, I retrieve the two tempvars, and assign them to local variables, which I then reference in the WHERE clause of my SELECT statement. Finally, I open a record set with the SELECT statment, and assign the me.controlsource property of the report to the SELECT. The SELECT stmt is:

slct1 = "SELECT [Sched].skdDate,[sched].skdTime, [sched].Office, [sched].Lname, [sched].Fname, [sched].Phone," _
& " [sched].status FROM Sched WHERE [Sched].skdDate = '" & rptDate & "' AND " _
& " [Sched].Office = '" & rptOffice & "' ORDER BY [Sched].skdTime"

Where rptDate and rptOffice are the parameters to the WHERE clause.

When I execute the SELECT statement in the Open_Report, I get the dreaded
Enter Parameter Value dialog box. The records I need are dependent on the Date and Office that I solicit in the form that executes the report.

How can I accomplish what I want, without getting the EPV dialog box?
Aug 15 '07 #1
Share this Question
Share on Google+
3 Replies


puppydogbuddy
Expert 100+
P: 1,923
I have written a report that lists the Patient Id, phone number &ct, for a specific Dr's office and a specific date.To execute the report and attendant query, I use a form to get the Date and Office Name. I then set me.application.tempvars('1") and me.application.tempvars("2") to the Date and Office of interest. In the Open_Report subroutine, I retrieve the two tempvars, and assign them to local variables, which I then reference in the WHERE clause of my SELECT statement. Finally, I open a record set with the SELECT statment, and assign the me.controlsource property of the report to the SELECT. The SELECT stmt is:

slct1 = "SELECT [Sched].skdDate,[sched].skdTime, [sched].Office, [sched].Lname, [sched].Fname, [sched].Phone," _
& " [sched].status FROM Sched WHERE [Sched].skdDate = '" & rptDate & "' AND " _
& " [Sched].Office = '" & rptOffice & "' ORDER BY [Sched].skdTime"

Where rptDate and rptOffice are the parameters to the WHERE clause.

When I execute the SELECT statement in the Open_Report, I get the dreaded
Enter Parameter Value dialog box. The records I need are dependent on the Date and Office that I solicit in the form that executes the report.

How can I accomplish what I want, without getting the EPV dialog box?
Try declaring your parameters in the list of parameters, and see if that helps. This is an optional one-time step, but may help Access in determining how to handle the parameters at run time. To declare your parameters, right mouse click on the top part of the query and choose Parameters. A parameter declaration form appears to let you list each parameter name and its type:

--------------------------------------------------------------------------------
Aug 16 '07 #2

P: 7
You get a message box asking you to enter data when access can't find the reference you are talking about. I have run into this sort of thing when I have renamed complex queries based off of queries.
The first thing I'd check is the spelling of all the tables, sub-queries and fields referenced to make sure it matches.

Bottom line, I think is you are pointing Access to a table or query definition that it can't find because either the name doesn't match or you have omitted a field that you are referencing.
Aug 16 '07 #3

P: 1
Dear all i have a problem
i have a database is set it there . but i want to create a report which i can choose the date for example, before i get the report i want to write the date like from 12-12-2006 to 12-10-207 and then display the details in these perioud of time is that possible , the date will be change ..
it is urgent , plz any one can help
Aug 16 '07 #4

Post your reply

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