pass query parameter value to report

F

freddyvet

I have an Access2007 report. Its data source is a query which has parameters.
So the user opens the report and the query asks for the dates to be included.
How can I pass these dates back to the report to appear in the header?
 
K

Ken Snell [MVP]

Add a calculated field to the query for each parameter:

FirstParameter: [ActualTextOfParameter]

etc.


So the SQL statement, generically, would look like this:

SELECT Field1, Field2, [ActualTextOfParameter] AS FirstParameter
FROM TableName
WHERE Field1 = [ActualTextOfParameter];


Then bind a textbox to the FirstParameter field from the query.


Alternatively, use a form to run the report, and have a textbox on the form
that contains the parameter value. Then let the query and the report both
read the value from the form's textbox control.
 
C

Clifford Bass

Hi Ken,

That is more effort than is needed. It gets passed through magically
without the need to put it in the query explicitly. Just create an unbound
text box and place this in it:

=[ActualTextOfParameter]

You can also do other stuff as if it was an actual field:

=Format$([Please enter start date:], "yyyy/mm/dd")

Clifford Bass

Ken Snell said:
Add a calculated field to the query for each parameter:

FirstParameter: [ActualTextOfParameter]

etc.


So the SQL statement, generically, would look like this:

SELECT Field1, Field2, [ActualTextOfParameter] AS FirstParameter
FROM TableName
WHERE Field1 = [ActualTextOfParameter];


Then bind a textbox to the FirstParameter field from the query.


Alternatively, use a form to run the report, and have a textbox on the form
that contains the parameter value. Then let the query and the report both
read the value from the form's textbox control.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


freddyvet said:
I have an Access2007 report. Its data source is a query which has
parameters.
So the user opens the report and the query asks for the dates to be
included.
How can I pass these dates back to the report to appear in the header?
 
K

Klatuu

That would be fine if the parameter value is always the same, but even then I
would not use that technique.
If the parameter is to filter the report's record source query, don't. Use
the Where argument of the OpenReport method.
If the value something to be displayed on the report, provide the user with
text boxes to enter the parameter values, then reference the form control in
the control source of the text box on the report:

=Forms!MyForm!SomeTextBox
--
Dave Hargis, Microsoft Access MVP


Clifford Bass said:
Hi Ken,

That is more effort than is needed. It gets passed through magically
without the need to put it in the query explicitly. Just create an unbound
text box and place this in it:

=[ActualTextOfParameter]

You can also do other stuff as if it was an actual field:

=Format$([Please enter start date:], "yyyy/mm/dd")

Clifford Bass

Ken Snell said:
Add a calculated field to the query for each parameter:

FirstParameter: [ActualTextOfParameter]

etc.


So the SQL statement, generically, would look like this:

SELECT Field1, Field2, [ActualTextOfParameter] AS FirstParameter
FROM TableName
WHERE Field1 = [ActualTextOfParameter];


Then bind a textbox to the FirstParameter field from the query.


Alternatively, use a form to run the report, and have a textbox on the form
that contains the parameter value. Then let the query and the report both
read the value from the form's textbox control.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


freddyvet said:
I have an Access2007 report. Its data source is a query which has
parameters.
So the user opens the report and the query asks for the dates to be
included.
How can I pass these dates back to the report to appear in the header?
 
C

Clifford Bass

Hi Dave,

I do not understand why you would say that. Maybe we are talking
apples and oranges? I am addressing specifically what the original poster
asked about; the ability to DISPLAY within a textbox, within a report the
value(s) a user has entered into the report's record source query's parameter
request box. Nothing more. I have used that method just fine without any
troubles for quite some time.

Clifford Bass
 
K

Klatuu

I think I misunderstood your post. When you put in
=[ActualTextOfParameter]
I took that to mean an actual hard coded value. Now that I have sobered up
:) it makes sense.
 
C

Clifford Bass

Hi Dave,

That makes sense--I can see that it could be misunderstood. I probably
should have stuck with a specific text instead of Ken's general text. Lesson
learned for me too.

Clifford
 
K

Ken Snell [MVP]

Interesting... never thought of trying this before. Thanks.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


Clifford Bass said:
Hi Ken,

That is more effort than is needed. It gets passed through magically
without the need to put it in the query explicitly. Just create an
unbound
text box and place this in it:

=[ActualTextOfParameter]

You can also do other stuff as if it was an actual field:

=Format$([Please enter start date:], "yyyy/mm/dd")

Clifford Bass

Ken Snell said:
Add a calculated field to the query for each parameter:

FirstParameter: [ActualTextOfParameter]

etc.


So the SQL statement, generically, would look like this:

SELECT Field1, Field2, [ActualTextOfParameter] AS FirstParameter
FROM TableName
WHERE Field1 = [ActualTextOfParameter];


Then bind a textbox to the FirstParameter field from the query.


Alternatively, use a form to run the report, and have a textbox on the
form
that contains the parameter value. Then let the query and the report both
read the value from the form's textbox control.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


freddyvet said:
I have an Access2007 report. Its data source is a query which has
parameters.
So the user opens the report and the query asks for the dates to be
included.
How can I pass these dates back to the report to appear in the header?
 
C

Clifford Bass

Hi Ken,

You are welcome. I think I may have stumbled upon it through trial and
error or maybe by accident.

Clifford Bass
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top