Solved Query does not Export when Column Heading are Used (1 Viewer)

[RogerCooper]

A customer wants me to e-mail a spreadsheet showing sales and columns for each month (even future months). Here is a simplified version of the query.

TRANSFORM Sum(dbo_History.Net_Sales) AS SumOfNet_Sales
SELECT dbo_History.CUSTOMER_NAME AS [Customer Name]
FROM dbo_History
WHERE (((dbo_History.BUYING_GROUP)="ITEG") AND ((dbo_History.YEAR)=Year(Date())))
GROUP BY dbo_History.CUSTOMER_NAME
ORDER BY dbo_History.CUSTOMER_NAME, dbo_History.Month
PIVOT dbo_History.Month In (1,2,3,4,5,6,7,8,9,10,11,12);

The recordset appears on the screen correctly. When I export it as a spreadsheet, only the header row appears, all other rows are blank.

If I don't specify the column headings it shows the numbers correctly, but it does not show columns for future months as the customer has requested.

Is this a bug in Access?

I probably could work around this issue by creating a table and then exporting the table, but I am looking for a better solution

 

[theDBguy]

How exactly are you exporting the query to Excel? Are you using code or the Wizard?

 

[RogerCooper]

I have used code and exported it manually. The result is the same.

 

[Pat Hartman]

It is Access being smarter than you are rather than a bug.
Try exporting as a .csv

 

[RogerCooper]

Same result with a CSV, only the header row appears.

 

[ebs17]

Try encapsulating the crosstab query in a select query:

SELECT X.* FROM
(TRANSFORM ...) AS X

ORDER BY ... dbo_History.Month

What is this good for?

 

[Pat Hartman]

Show us what the result looks like when you run the crosstab query in Access.

Access is not exporting empty rows and you seem to think it should.

 

[theDBguy]

I have used code and exported it manually. The result is the same.

I just gave it a try, and here's what I got.

 

[RogerCooper]

Getting rid of the OrderBy Month fixed it. Thank you for the help.