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 SenibaldiNevermind, I didn't realized that I had to leave the original criteria in the
actual query.
Thanks.
Post by Mark SenibaldiHi, 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 BrandtPost by megeorgeJohn, 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