Date not Properly Formatted in Crosstab Query

[eliascm]

I have a crosstab query based on a single table. The query works as expected except for one problem: The column heading is report date from the record field report_date. In the table report_date is formatted mmm-yyyy. The date is properly formatted in a datasheet view of the table and in a simple select query based on the table and in a form based on the table. However, the columns in the crosstab query are formatted m/1/yyyy. I have tried using a format statement for the date and converting the resulting string to a date using the function CDate (the conversion is necessary to sort the date as a date and not a string). Nothing I have tried results in the desired format for the date columns in the crosstab query.

Please help me if you can. This may be a bug, but I don't know how to inform Microsoft about the problem.



Thanks!

[June7]

Can you provide project for analysis?

[eliascm]

Attached is the database.

[June7]

If you want the columns in chronological order by month and year, will have to live with that structure. It is the nature of crosstab with dates.

The alternative is a select query with a field that calculates the month and year as a string and use that query as source for the crosstab and that constructed field as the crosstab columns. That means the column order will be alpha instead of chrono.

[eliascm]

Thanks for the reply. I would much rather stick with the current date format than have the dates sorted as strings.

Charles