Discussion:
How can i pass a parameter to crosstab query
(too old to reply)
megeorge
2005-01-12 17:47:05 UTC
Permalink
I want to set the criteria for the cross tab with a value entered on a form.
I do this with normal queries but the cross tab won't recognize the field
from the form.
Duane Hookom
2005-01-12 20:45:51 UTC
Permalink
You must specify the data types of the parameters. Select Query->Parameters
and enter you parameter(s) and data types.
--
Duane Hookom
MS Access MVP
Post by megeorge
I want to set the criteria for the cross tab with a value entered on a form.
I do this with normal queries but the cross tab won't recognize the field
from the form.
megeorge
2005-01-12 21:03:03 UTC
Permalink
Duane, thanks for the feedback but I knew how to do that. I want to run the
query from form where the user can enter a value and then feed that value to
the parameters. I was hoping that I could specify it somehow from the VBA
code from the "DoCmd.OpenQuery" or if there was some other way to execute the
query and pass the parameter values.
Post by Duane Hookom
You must specify the data types of the parameters. Select Query->Parameters
and enter you parameter(s) and data types.
--
Duane Hookom
MS Access MVP
Post by megeorge
I want to set the criteria for the cross tab with a value entered on a form.
I do this with normal queries but the cross tab won't recognize the field
from the form.
John Spencer (MVP)
2005-01-12 22:34:19 UTC
Permalink
I think you may have misunderstood Duane's response.

With a crosstab query you MUST declare your parameters and if any
other queries are used in the crosstab their parameters must also be declared.

Open the query in design mode
Select Query: Parameters from the Menu
Fill in the EXACT name of the parameter in column 1
Select the data type of the parameter in column 2
Post by megeorge
Duane, thanks for the feedback but I knew how to do that. I want to run the
query from form where the user can enter a value and then feed that value to
the parameters. I was hoping that I could specify it somehow from the VBA
code from the "DoCmd.OpenQuery" or if there was some other way to execute the
query and pass the parameter values.
Post by Duane Hookom
You must specify the data types of the parameters. Select Query->Parameters
and enter you parameter(s) and data types.
--
Duane Hookom
MS Access MVP
Post by megeorge
I want to set the criteria for the cross tab with a value entered on a form.
I do this with normal queries but the cross tab won't recognize the field
from the form.
megeorge
2005-01-12 23:17:01 UTC
Permalink
John, Thanks for responding. No I understood Duanes response. I have the
parameters declared as you all have instructed. When I run the query it
prompts me for the value for the parameter. for example one of the parameters
is for a forecast id. I want the user to first select a valid forecast id
from a drop down list on a form. then use that value in the parameter for the
cross tab query.
Post by John Spencer (MVP)
I think you may have misunderstood Duane's response.
With a crosstab query you MUST declare your parameters and if any
other queries are used in the crosstab their parameters must also be declared.
Open the query in design mode
Select Query: Parameters from the Menu
Fill in the EXACT name of the parameter in column 1
Select the data type of the parameter in column 2
Post by megeorge
Duane, thanks for the feedback but I knew how to do that. I want to run the
query from form where the user can enter a value and then feed that value to
the parameters. I was hoping that I could specify it somehow from the VBA
code from the "DoCmd.OpenQuery" or if there was some other way to execute the
query and pass the parameter values.
Post by Duane Hookom
You must specify the data types of the parameters. Select Query->Parameters
and enter you parameter(s) and data types.
--
Duane Hookom
MS Access MVP
Post by megeorge
I want to set the criteria for the cross tab with a value entered on a form.
I do this with normal queries but the cross tab won't recognize the field
from the form.
Rick Brandt
2005-01-13 00:25:27 UTC
Permalink
Post by megeorge
John, Thanks for responding. No I understood Duanes response. I have
the parameters declared as you all have instructed. When I run the
query it prompts me for the value for the parameter. for example one
of the parameters is for a forecast id. I want the user to first
select a valid forecast id from a drop down list on a form. then use
that value in the parameter for the cross tab query.
No, you still don't understand. You seem to be making the assumption that a
"parameter" in a query is something that prompts you for input when the query is
run and you want to eliminate that and use a form reference instead. What the
previous responders have been attempting to make you understand is that a form
reference is also a parameter (just a different kind).

In the following query...

SELECT * FROM SomeTable
WHERE SomeField = Forms!SomeForm!SomeControl

...the string "Forms!SomeForm!SomeControl" is considered a parameter. It is
just not a self-prompting parameter like what you are thinking of. Most queries
in Access will use such a parameter without you having to explicitly go into the
parameters dialog of the query and make an entry. The parameter is just
resolved on the fly.

A crosstab query is a bit more stubborn and insists that all parameters be
explicitly listed in the parameters dialog. So if I wanted to use the above
form reference in a crosstab query I would have to open the parameters list and
enter "Forms!SomeForm!SomeControl" as a parameter and indicate the DataType.
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com
megeorge
2005-01-13 02:43:01 UTC
Permalink
Thank you very much. Sorry for being so thick.
Post by Rick Brandt
Post by megeorge
John, Thanks for responding. No I understood Duanes response. I have
the parameters declared as you all have instructed. When I run the
query it prompts me for the value for the parameter. for example one
of the parameters is for a forecast id. I want the user to first
select a valid forecast id from a drop down list on a form. then use
that value in the parameter for the cross tab query.
No, you still don't understand. You seem to be making the assumption that a
"parameter" in a query is something that prompts you for input when the query is
run and you want to eliminate that and use a form reference instead. What the
previous responders have been attempting to make you understand is that a form
reference is also a parameter (just a different kind).
In the following query...
SELECT * FROM SomeTable
WHERE SomeField = Forms!SomeForm!SomeControl
....the string "Forms!SomeForm!SomeControl" is considered a parameter. It is
just not a self-prompting parameter like what you are thinking of. Most queries
in Access will use such a parameter without you having to explicitly go into the
parameters dialog of the query and make an entry. The parameter is just
resolved on the fly.
A crosstab query is a bit more stubborn and insists that all parameters be
explicitly listed in the parameters dialog. So if I wanted to use the above
form reference in a crosstab query I would have to open the parameters list and
enter "Forms!SomeForm!SomeControl" as a parameter and indicate the DataType.
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com
HSL
2005-01-13 14:51:03 UTC
Permalink
I am doing a similar crosstab query but when I enter the parameters in the
dialog box and try to run the query, I get a message about invalid
bracketing. I am entering the parameter as follows:

Forms!frm_BuildRptbySrvyProd!Test Product 1

Does anyone know what I could be doing wrong?
Post by megeorge
Thank you very much. Sorry for being so thick.
Post by Rick Brandt
Post by megeorge
John, Thanks for responding. No I understood Duanes response. I have
the parameters declared as you all have instructed. When I run the
query it prompts me for the value for the parameter. for example one
of the parameters is for a forecast id. I want the user to first
select a valid forecast id from a drop down list on a form. then use
that value in the parameter for the cross tab query.
No, you still don't understand. You seem to be making the assumption that a
"parameter" in a query is something that prompts you for input when the query is
run and you want to eliminate that and use a form reference instead. What the
previous responders have been attempting to make you understand is that a form
reference is also a parameter (just a different kind).
In the following query...
SELECT * FROM SomeTable
WHERE SomeField = Forms!SomeForm!SomeControl
....the string "Forms!SomeForm!SomeControl" is considered a parameter. It is
just not a self-prompting parameter like what you are thinking of. Most queries
in Access will use such a parameter without you having to explicitly go into the
parameters dialog of the query and make an entry. The parameter is just
resolved on the fly.
A crosstab query is a bit more stubborn and insists that all parameters be
explicitly listed in the parameters dialog. So if I wanted to use the above
form reference in a crosstab query I would have to open the parameters list and
enter "Forms!SomeForm!SomeControl" as a parameter and indicate the DataType.
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com
Duane Hookom
2005-01-13 15:04:58 UTC
Permalink
This is a bit of a bug. You can open your query in SQL view and correct the
brackets in your parameter clause.
--
Duane Hookom
MS Access MVP
Post by HSL
I am doing a similar crosstab query but when I enter the parameters in the
dialog box and try to run the query, I get a message about invalid
Forms!frm_BuildRptbySrvyProd!Test Product 1
Does anyone know what I could be doing wrong?
Post by megeorge
Thank you very much. Sorry for being so thick.
Post by Rick Brandt
Post by megeorge
John, Thanks for responding. No I understood Duanes response. I have
the parameters declared as you all have instructed. When I run the
query it prompts me for the value for the parameter. for example one
of the parameters is for a forecast id. I want the user to first
select a valid forecast id from a drop down list on a form. then use
that value in the parameter for the cross tab query.
No, you still don't understand. You seem to be making the assumption that a
"parameter" in a query is something that prompts you for input when the query is
run and you want to eliminate that and use a form reference instead.
What the
previous responders have been attempting to make you understand is that a form
reference is also a parameter (just a different kind).
In the following query...
SELECT * FROM SomeTable
WHERE SomeField = Forms!SomeForm!SomeControl
....the string "Forms!SomeForm!SomeControl" is considered a parameter.
It is
just not a self-prompting parameter like what you are thinking of.
Most queries
in Access will use such a parameter without you having to explicitly go into the
parameters dialog of the query and make an entry. The parameter is just
resolved on the fly.
A crosstab query is a bit more stubborn and insists that all parameters be
explicitly listed in the parameters dialog. So if I wanted to use the above
form reference in a crosstab query I would have to open the parameters list and
enter "Forms!SomeForm!SomeControl" as a parameter and indicate the DataType.
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com
HSL
2005-01-13 15:09:02 UTC
Permalink
I have a similar crosstab query I am working on. I have listed the
parameters in the dialog box but when I try to run it, it says that the
Microsoft Jet Database engine does not recognize the field. Is it a bracket
issue? Do you or don't you use brackets?
Post by megeorge
Thank you very much. Sorry for being so thick.
Post by Rick Brandt
Post by megeorge
John, Thanks for responding. No I understood Duanes response. I have
the parameters declared as you all have instructed. When I run the
query it prompts me for the value for the parameter. for example one
of the parameters is for a forecast id. I want the user to first
select a valid forecast id from a drop down list on a form. then use
that value in the parameter for the cross tab query.
No, you still don't understand. You seem to be making the assumption that a
"parameter" in a query is something that prompts you for input when the query is
run and you want to eliminate that and use a form reference instead. What the
previous responders have been attempting to make you understand is that a form
reference is also a parameter (just a different kind).
In the following query...
SELECT * FROM SomeTable
WHERE SomeField = Forms!SomeForm!SomeControl
....the string "Forms!SomeForm!SomeControl" is considered a parameter. It is
just not a self-prompting parameter like what you are thinking of. Most queries
in Access will use such a parameter without you having to explicitly go into the
parameters dialog of the query and make an entry. The parameter is just
resolved on the fly.
A crosstab query is a bit more stubborn and insists that all parameters be
explicitly listed in the parameters dialog. So if I wanted to use the above
form reference in a crosstab query I would have to open the parameters list and
enter "Forms!SomeForm!SomeControl" as a parameter and indicate the DataType.
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com
megeorge
2005-01-13 15:17:07 UTC
Permalink
Thanks to everyone I have it working now. Inresponse to HSL Yes I did include
the brackets and it's working for me. Thanks again.
Post by HSL
I have a similar crosstab query I am working on. I have listed the
parameters in the dialog box but when I try to run it, it says that the
Microsoft Jet Database engine does not recognize the field. Is it a bracket
issue? Do you or don't you use brackets?
Post by megeorge
Thank you very much. Sorry for being so thick.
Post by Rick Brandt
Post by megeorge
John, Thanks for responding. No I understood Duanes response. I have
the parameters declared as you all have instructed. When I run the
query it prompts me for the value for the parameter. for example one
of the parameters is for a forecast id. I want the user to first
select a valid forecast id from a drop down list on a form. then use
that value in the parameter for the cross tab query.
No, you still don't understand. You seem to be making the assumption that a
"parameter" in a query is something that prompts you for input when the query is
run and you want to eliminate that and use a form reference instead. What the
previous responders have been attempting to make you understand is that a form
reference is also a parameter (just a different kind).
In the following query...
SELECT * FROM SomeTable
WHERE SomeField = Forms!SomeForm!SomeControl
....the string "Forms!SomeForm!SomeControl" is considered a parameter. It is
just not a self-prompting parameter like what you are thinking of. Most queries
in Access will use such a parameter without you having to explicitly go into the
parameters dialog of the query and make an entry. The parameter is just
resolved on the fly.
A crosstab query is a bit more stubborn and insists that all parameters be
explicitly listed in the parameters dialog. So if I wanted to use the above
form reference in a crosstab query I would have to open the parameters list and
enter "Forms!SomeForm!SomeControl" as a parameter and indicate the DataType.
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com
Mark Senibaldi
2005-03-17 16:43:04 UTC
Permalink
Hi, this was all very helpful but how does the query know which field in the
query you are matching the form value (criteria) to?
Post by Rick Brandt
Post by megeorge
John, Thanks for responding. No I understood Duanes response. I have
the parameters declared as you all have instructed. When I run the
query it prompts me for the value for the parameter. for example one
of the parameters is for a forecast id. I want the user to first
select a valid forecast id from a drop down list on a form. then use
that value in the parameter for the cross tab query.
No, you still don't understand. You seem to be making the assumption that a
"parameter" in a query is something that prompts you for input when the query is
run and you want to eliminate that and use a form reference instead. What the
previous responders have been attempting to make you understand is that a form
reference is also a parameter (just a different kind).
In the following query...
SELECT * FROM SomeTable
WHERE SomeField = Forms!SomeForm!SomeControl
....the string "Forms!SomeForm!SomeControl" is considered a parameter. It is
just not a self-prompting parameter like what you are thinking of. Most queries
in Access will use such a parameter without you having to explicitly go into the
parameters dialog of the query and make an entry. The parameter is just
resolved on the fly.
A crosstab query is a bit more stubborn and insists that all parameters be
explicitly listed in the parameters dialog. So if I wanted to use the above
form reference in a crosstab query I would have to open the parameters list and
enter "Forms!SomeForm!SomeControl" as a parameter and indicate the DataType.
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com
Mark Senibaldi
2005-03-17 16:55:06 UTC
Permalink
Nevermind, I didn't realized that I had to leave the original criteria in the
actual query.
Thanks.
Post by Mark Senibaldi
Hi, this was all very helpful but how does the query know which field in the
query you are matching the form value (criteria) to?
Post by Rick Brandt
Post by megeorge
John, Thanks for responding. No I understood Duanes response. I have
the parameters declared as you all have instructed. When I run the
query it prompts me for the value for the parameter. for example one
of the parameters is for a forecast id. I want the user to first
select a valid forecast id from a drop down list on a form. then use
that value in the parameter for the cross tab query.
No, you still don't understand. You seem to be making the assumption that a
"parameter" in a query is something that prompts you for input when the query is
run and you want to eliminate that and use a form reference instead. What the
previous responders have been attempting to make you understand is that a form
reference is also a parameter (just a different kind).
In the following query...
SELECT * FROM SomeTable
WHERE SomeField = Forms!SomeForm!SomeControl
....the string "Forms!SomeForm!SomeControl" is considered a parameter. It is
just not a self-prompting parameter like what you are thinking of. Most queries
in Access will use such a parameter without you having to explicitly go into the
parameters dialog of the query and make an entry. The parameter is just
resolved on the fly.
A crosstab query is a bit more stubborn and insists that all parameters be
explicitly listed in the parameters dialog. So if I wanted to use the above
form reference in a crosstab query I would have to open the parameters list and
enter "Forms!SomeForm!SomeControl" as a parameter and indicate the DataType.
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com
Ang
2005-11-09 19:41:51 UTC
Permalink
I have a form with combo boxes where the user selects the criteria.

Then I have 3 buttons (so far) to open different reports filtered by the
selected criteria. The two that use select queries are working fine.

The cross tab query works for one of the boxes but if anything is selected
in the others I get

The Microsoft Jet database engine does not recongnize " as a valid field
name or expression.

The cross tab sql is

PARAMETERS [Forms]![frmReportCriteria]![Listmonthn] Text ( 255 ),
[Forms]![frmReportCriteria]![listFund] Text ( 255 ),
[Forms]![frmReportCriteria]![listFY] Text ( 255 ),
[Forms]![frmReportCriteria]![listdir] Text ( 255 ),
[Forms]![frmReportCriteria]![listdiv] Text ( 255 ),
[Forms]![frmReportCriteria]![listcategory] Text ( 255 );
TRANSFORM Sum(qryRptCriteria.TOTAL) AS SumOfTOTAL
SELECT qryRptCriteria.DIV, qryRptCriteria.[Fund Type],
Sum(qryRptCriteria.TOTAL) AS [Total Of TOTAL]
FROM qryRptCriteria
GROUP BY qryRptCriteria.DIV, qryRptCriteria.[Fund Type]
PIVOT qryRptCriteria.Category;


The vba for the button including the filter is:
Private Sub btnSummary_Click()
On Error GoTo Err_btnSummary_Click

Dim stDocName As String

Dim StrWhere As String

'FY- limit to selection unless null
If Not IsNull(Me.listFY) Then
StrWhere = StrWhere & "([FY]=""" & Me.listFY & """) and "
End If


'Fund Type - limit to selection unless null
If Not IsNull(Me.Listfund) Then
StrWhere = StrWhere & "([fund type]=""" & Me.Listfund & """) and "
End If

'Directorate - Limit to list unless null


If Not IsNull(Me.listDir) Then
StrWhere = StrWhere & "([dir] = """ & Me.listDir & """) and "
End If



'Divison - limit to selection unless null
If Not IsNull(Me.listdiv) Then
StrWhere = StrWhere & "([div] = """ & Me.listdiv & """) and "
End If
'Month

If Not IsNull(Me.Listmonthn) Then
StrWhere = StrWhere & "([MonthN] = """ & Me.Listmonthn & """) and "
End If

'Category
If Not IsNull(Me.Listcategory) Then
StrWhere = StrWhere & "([Category] = """ & Me.Listcategory & """) and "
End If

'Chop off the trailing and
lngLen = Len(StrWhere) - 5
If lngLen > 0 Then
StrWhere = Left$(StrWhere, lngLen)
End If

Debug.Print StrWhere


stDocName = "RptCriteriaCrosstab"
DoCmd.OpenReport stDocName, acPreview, , StrWhere


Exit_btnSummary_Click:
Exit Sub

Err_btnSummary_Click:
MsgBox Err.Description
Resume Exit_btnSummary_Click

End Sub
Post by Mark Senibaldi
Nevermind, I didn't realized that I had to leave the original criteria in the
actual query.
Thanks.
Post by Mark Senibaldi
Hi, this was all very helpful but how does the query know which field in the
query you are matching the form value (criteria) to?
Post by Rick Brandt
Post by megeorge
John, Thanks for responding. No I understood Duanes response. I have
the parameters declared as you all have instructed. When I run the
query it prompts me for the value for the parameter. for example one
of the parameters is for a forecast id. I want the user to first
select a valid forecast id from a drop down list on a form. then use
that value in the parameter for the cross tab query.
No, you still don't understand. You seem to be making the assumption that a
"parameter" in a query is something that prompts you for input when the query is
run and you want to eliminate that and use a form reference instead. What the
previous responders have been attempting to make you understand is that a form
reference is also a parameter (just a different kind).
In the following query...
SELECT * FROM SomeTable
WHERE SomeField = Forms!SomeForm!SomeControl
....the string "Forms!SomeForm!SomeControl" is considered a parameter. It is
just not a self-prompting parameter like what you are thinking of. Most queries
in Access will use such a parameter without you having to explicitly go into the
parameters dialog of the query and make an entry. The parameter is just
resolved on the fly.
A crosstab query is a bit more stubborn and insists that all parameters be
explicitly listed in the parameters dialog. So if I wanted to use the above
form reference in a crosstab query I would have to open the parameters list and
enter "Forms!SomeForm!SomeControl" as a parameter and indicate the DataType.
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com
l***@gmail.com
2017-07-27 16:25:01 UTC
Permalink
I know this is a 12 year old discussion, but I have to tell you how much I love you right now. THANK YOU RICK BRANDT!!!
Ann B
2008-09-03 13:41:01 UTC
Permalink
I have this same problem but the Crosstab query is only a linked query-and
the parameters I am trying to run the query on are not on a field I am
pulling in from the crosstab query -but the error messages I am getting are
acting like this. I tried placing the parameters in the dialog box, and I
got the invalid bracketing error- I went into SQL to remove them and got
additional errors then when trying to save. Should I place the parameters
for this query in the linked crosstab query even though they don't reference
any fields?
Post by John Spencer (MVP)
I think you may have misunderstood Duane's response.
With a crosstab query you MUST declare your parameters and if any
other queries are used in the crosstab their parameters must also be declared.
Open the query in design mode
Select Query: Parameters from the Menu
Fill in the EXACT name of the parameter in column 1
Select the data type of the parameter in column 2
Post by megeorge
Duane, thanks for the feedback but I knew how to do that. I want to run the
query from form where the user can enter a value and then feed that value to
the parameters. I was hoping that I could specify it somehow from the VBA
code from the "DoCmd.OpenQuery" or if there was some other way to execute the
query and pass the parameter values.
Post by Duane Hookom
You must specify the data types of the parameters. Select Query->Parameters
and enter you parameter(s) and data types.
--
Duane Hookom
MS Access MVP
Post by megeorge
I want to set the criteria for the cross tab with a value entered on a form.
I do this with normal queries but the cross tab won't recognize the field
from the form.
John Spencer
2008-09-03 15:44:24 UTC
Permalink
See my reply in your earlier thread.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
Post by Ann B
I have this same problem but the Crosstab query is only a linked query-and
the parameters I am trying to run the query on are not on a field I am
pulling in from the crosstab query -but the error messages I am getting are
acting like this. I tried placing the parameters in the dialog box, and I
got the invalid bracketing error- I went into SQL to remove them and got
additional errors then when trying to save. Should I place the parameters
for this query in the linked crosstab query even though they don't reference
any fields?
Post by John Spencer (MVP)
I think you may have misunderstood Duane's response.
With a crosstab query you MUST declare your parameters and if any
other queries are used in the crosstab their parameters must also be declared.
Open the query in design mode
Select Query: Parameters from the Menu
Fill in the EXACT name of the parameter in column 1
Select the data type of the parameter in column 2
Post by megeorge
Duane, thanks for the feedback but I knew how to do that. I want to run the
query from form where the user can enter a value and then feed that value to
the parameters. I was hoping that I could specify it somehow from the VBA
code from the "DoCmd.OpenQuery" or if there was some other way to execute the
query and pass the parameter values.
Post by Duane Hookom
You must specify the data types of the parameters. Select Query->Parameters
and enter you parameter(s) and data types.
--
Duane Hookom
MS Access MVP
Post by megeorge
I want to set the criteria for the cross tab with a value entered on a form.
I do this with normal queries but the cross tab won't recognize the field
from the form.
p***@gmail.com
2019-07-16 17:07:44 UTC
Permalink
Post by megeorge
I want to set the criteria for the cross tab with a value entered on a form.
I do this with normal queries but the cross tab won't recognize the field
from the form.
How do I know what the query parameter EXACT name is? Ex: Forms!SomeForm!SomeControl I'm just getting back into Access after many years away and the cobwebs in my brain are thick.
Loading...