Crosstab query based on combo box criteria

  • Thread starter Thread starter Rohit Thomas
  • Start date Start date
R

Rohit Thomas

Hello all,

Earlier I posted a message asking whether it was possible
to create a report based on two crosstab queries (Subject:
Similar crosstab queries on one report) to which Duane
Hookom replied with some great suggestions.

Using his methodology, I was able to create a single
crosstab query that allowed me to count the # of
adjustments and sum the dollar amounts of these
adjustments, however the query displays an error (The
Microsoft Jet database engine does not recognize '[Forms]!
[frmPrintPreviewReports]![CmbMonth]' as a valid field name
or expression.) when I add a "WHERE" clause filter to the
SQL statement. The filter is based on the result of a
combox box on a form that is loaded when the event fires
to print or preview the report. I am confused as to why
this should not work...any thoughts on what I may be doing
wrong.

Thanks for the help in advance,
Rohit Thomas

Here's is my SQL statement for the crosstab query. This
query is based on a union query:

TRANSFORM Sum(IIf([FldName]="MonthAdjCount",[Adj Count],
[Adj Amount])) AS [Adj Count Amount]
SELECT [qryBank001TellerFifty&UnderAll].[Bank ID],
[qryBank001TellerFifty&UnderAll].[Transaction Office],
[qryBank001TellerFifty&UnderAll].[Branch Name],
[qryBank001TellerFifty&UnderAll].[Teller ID],
[qryBank001TellerFifty&UnderAll].[First Name],
[qryBank001TellerFifty&UnderAll].[Last Name], Count(IIf
([FldName]="MonthAdjCount",[Credit Or Debit])) AS [Adj
Count Total], Sum(IIf([FldName]="MonthAdjCount",[Adj
Amount])) AS [Adj Amount Total]
FROM [qryBank001TellerFifty&UnderAll], tblXTabTellerAdj,
tblFileDate
WHERE ((([qryBank001TellerFifty&UnderAll].DateYear)=Year
([tblFileDate]![ImpFileDate])) AND
(([qryBank001TellerFifty&UnderAll].MonthName)=[Forms]!
[frmPrintPreviewReports]![CmbMonth]))
GROUP BY [qryBank001TellerFifty&UnderAll].[Bank ID],
[qryBank001TellerFifty&UnderAll].[Transaction Office],
[qryBank001TellerFifty&UnderAll].[Branch Name],
[qryBank001TellerFifty&UnderAll].[Teller ID],
[qryBank001TellerFifty&UnderAll].[First Name],
[qryBank001TellerFifty&UnderAll].[Last Name]
PIVOT [FldName] & Month([FileDate]);
 
Crosstab queries demand that you specify the data types of the parameters.
Select Query|Parameters and enter
Forms]![frmPrintPreviewReports]![CmbMonth] Text
 
Back
Top