Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

How can i pass a parameter to crosstab query

2,203 views
Skip to first unread message

megeorge

unread,
Jan 12, 2005, 12:47:05 PM1/12/05
to
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

unread,
Jan 12, 2005, 3:45:51 PM1/12/05
to
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


"megeorge" <mege...@discussions.microsoft.com> wrote in message
news:EF2E1029-DCC3-4B8A...@microsoft.com...

megeorge

unread,
Jan 12, 2005, 4:03:03 PM1/12/05
to
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.

John Spencer (MVP)

unread,
Jan 12, 2005, 5:34:19 PM1/12/05
to
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

megeorge

unread,
Jan 12, 2005, 6:17:01 PM1/12/05
to
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.

Rick Brandt

unread,
Jan 12, 2005, 7:25:27 PM1/12/05
to
megeorge wrote:
> 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

unread,
Jan 12, 2005, 9:43:01 PM1/12/05
to
Thank you very much. Sorry for being so thick.

"Rick Brandt" wrote:

> megeorge wrote:
> > 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

HSL

unread,
Jan 13, 2005, 9:51:03 AM1/13/05
to
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?

Duane Hookom

unread,
Jan 13, 2005, 10:04:58 AM1/13/05
to
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


"HSL" <H...@discussions.microsoft.com> wrote in message
news:6433372A-2AFC-4D34...@microsoft.com...

HSL

unread,
Jan 13, 2005, 10:09:02 AM1/13/05
to
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?

megeorge

unread,
Jan 13, 2005, 10:17:07 AM1/13/05
to
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.

Mark Senibaldi

unread,
Mar 17, 2005, 11:43:04 AM3/17/05
to
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?

"Rick Brandt" wrote:

> megeorge wrote:
> > 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

Mark Senibaldi

unread,
Mar 17, 2005, 11:55:06 AM3/17/05
to
Nevermind, I didn't realized that I had to leave the original criteria in the
actual query.
Thanks.

Ang

unread,
Nov 9, 2005, 2:41:51 PM11/9/05
to
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

Ann B

unread,
Sep 3, 2008, 9:41:01 AM9/3/08
to
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?

John Spencer

unread,
Sep 3, 2008, 11:44:24 AM9/3/08
to
See my reply in your earlier thread.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

larah...@gmail.com

unread,
Jul 27, 2017, 12:25:04 PM7/27/17
to
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!!!

peoria...@gmail.com

unread,
Jul 16, 2019, 1:07:45 PM7/16/19
to
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.
0 new messages