pass query parameter value to report

[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?

 

[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.

 

[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

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/

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?

 

[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

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

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/

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?

 

[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

 

[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.

 

[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

 

[Ken Snell [MVP]]

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

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

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

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/

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?

 

[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