How can i pass a parameter to crosstab query
megeorge
I do this with normal queries but the cross tab won't recognize the field
from the form.
Duane Hookom
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
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)
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
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
> 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
"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
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
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
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
the brackets and it's working for me. Thanks again.
Mark Senibaldi
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
actual query.
Thanks.
Ang
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
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
'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
