Report with Crosstab query not working

  • Thread starter Thread starter Angel
  • Start date Start date
A

Angel

I have a report that pulls all sales using a crosstab query to show the data
in a monthly format. However, I want the report to filter data based on the
value I enter on a field (Combo box) in a form (UnitSales_frm). When I type
the vaule (Customer's name) myself in the criteria of the crosstab query it
works fine, but when I put in the criteria field the following sintax it
does not work.
[Forms]![UnitSales_frm]![CustValue]
I get an error saying "The Microsoft Jet Database engine does not recognize
[Forms]![UnitSales_frm]![CustValue] as a valid field name or expression. I
do have the form loaded and field contains data but the info is not passed
properly.
Is there something wrong with my syntax or should I be using something
else?
Here is the complete SQL string.

TRANSFORM Sum([Sales by Everything].INVOICED_QTY) AS [The Value]
SELECT [Sales by Everything].REGION, [Sales by Everything].CUST_ID, [Sales
by Everything].CUST_NAME, IIf([Sales by Everything]![FAMILY_NAME] Is
Null,"MISC",[Sales by Everything]![FAMILY_NAME]) AS FAMILY_NAME, [Sales by
Everything].GRP_TCH_CD, [Sales by Everything].ITEM, [Sales by
Everything].ITEM_DESC, Sum([Sales by Everything].INVOICED_QTY) AS [Row
Summary]
FROM [Sales by Everything]
WHERE ((([Sales by
Everything].CUST_ID)=[Forms]![UnitSales_frm]![CustValue]))
GROUP BY [Sales by Everything].REGION, [Sales by Everything].CUST_ID, [Sales
by Everything].CUST_NAME, IIf([Sales by Everything]![FAMILY_NAME] Is
Null,"MISC",[Sales by Everything]![FAMILY_NAME]), [Sales by
Everything].GRP_TCH_CD, [Sales by Everything].ITEM, [Sales by
Everything].ITEM_DESC
PIVOT [Sales by Everything].ACCTG_PRD;

Any help is greatly appreciated.

Angel
 
Back
Top