Cross Tab Headings in a Report 1

[BenTitus]

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

 

[deanman]

Add this to the end of your SQL - In ("Mon","Tue","Wed","Thur","Fri","Sat","Sun"

So it looks like this.

PIVOT [CrossTab Query for Rigid - BWT].[Day of the Week]In ("Mon","Tue","Wed","Thur","Fri","Sat","Sun"

This will for the Query to have a column for each day.

 

[CosmoKramer]

You could use the In clause as part of your PIVOT statement to do two things:

* Define values for columns so that columns will appear even when there is no data for them
* Define the order of the columns

Something like this:

PIVOT [CrossTab Query for Rigid - BWT].[Day of the Week] In ('Monday', 'Tuesday', 'Wednesday', ....etc...);

 

[BenTitus]

Thanks I tried doing an IN with the pivot but was attempting a between withing the In which looking back was pretty stupid. Thanks alot