How can i pass a parameter to crosstab query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
You must specify the data types of the parameters. Select Query->Parameters
and enter you parameter(s) and data types.
 
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.
 
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
 
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.
 
megeorge said:
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.
Click to expand...

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.
 
Thank you very much. Sorry for being so thick.

Rick Brandt said:
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.
Click to expand...
 
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?
 
This is a bit of a bug. You can open your query in SQL view and correct the
brackets in your parameter clause.
 
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?
 
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.
 
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?
 
Nevermind, I didn't realized that I had to leave the original criteria in the
actual query.
Thanks.
 
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
 
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?
 
See my reply in your earlier thread.

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