Hello,
I am creating a report based off a cross tab query. The column headings are the days of the week (monday through friday) and there is a date parameter to select the week you want to view. My problem is that when a week does not contain data for all 5 days i get an error saying: The Microsoft Jet Database does not recognize " as a valid field name or expression. I have created 4 other reports for different products and the reports have worked fine(they have sales every day). The SQL for the cross tab query looks like this:
PARAMETERS [Start Date] DateTime, [End Date] DateTime;
TRANSFORM Sum([CrossTab Query for Rigid - BWT].Expr1) AS SumOfExpr1
SELECT [CrossTab Query for Rigid - BWT].[Part#], [CrossTab Query for Rigid - BWT].[Part Description]
FROM [CrossTab Query for Rigid - BWT]
WHERE ((([CrossTab Query for Rigid - BWT].[Ship Date]) Between [Start Date] And [End Date]))
GROUP BY [CrossTab Query for Rigid - BWT].[Part#], [CrossTab Query for Rigid - BWT].[Part Description]
PIVOT [CrossTab Query for Rigid - BWT].[Day of the Week];
Thanks in advance
I am creating a report based off a cross tab query. The column headings are the days of the week (monday through friday) and there is a date parameter to select the week you want to view. My problem is that when a week does not contain data for all 5 days i get an error saying: The Microsoft Jet Database does not recognize " as a valid field name or expression. I have created 4 other reports for different products and the reports have worked fine(they have sales every day). The SQL for the cross tab query looks like this:
PARAMETERS [Start Date] DateTime, [End Date] DateTime;
TRANSFORM Sum([CrossTab Query for Rigid - BWT].Expr1) AS SumOfExpr1
SELECT [CrossTab Query for Rigid - BWT].[Part#], [CrossTab Query for Rigid - BWT].[Part Description]
FROM [CrossTab Query for Rigid - BWT]
WHERE ((([CrossTab Query for Rigid - BWT].[Ship Date]) Between [Start Date] And [End Date]))
GROUP BY [CrossTab Query for Rigid - BWT].[Part#], [CrossTab Query for Rigid - BWT].[Part Description]
PIVOT [CrossTab Query for Rigid - BWT].[Day of the Week];
Thanks in advance