Crosstab Column Heading Sort

[Guest]

I am writing a report where the user specifies the beginning date and I go
get the information for the twelve month period that begins with that date.
Once I have the data I need to get a sum of hours worked for the month for
each employee.

The Data should look like this:

Name 02/2004 03/2004 04/2004 ...
Joe 1 3
Jane 3 2

I can accomplish this with a crosstab query but when I do Access displays
the columns in order by an alphanumeric sort. IE: 1/2004 10/2004 11/2004
2/2004

I can't fix this in the queries properties because I have no idea what
months I will be dealing with.

Can anyone help with this?

 

[Duane Hookom]

Use relative column headings rather than actual dates. If the "user
specifies the beginning date " using a control
[Forms]![frmDates]![txtBegDate], use a column heading expression like:

ColHead: "M" & DateDiff("m",[Forms]![frmDates]![txtBegDate],
[YourDateField])

Then set the column headings property to:
"M0","M1","M2",.."M11"
M0 will be results from the beginning date and M11 will be 11 months later.

 

[[MVP] S.Clark]

Use can try to use the column headings property of the query to specify the
column names in the order that you need. You may need to update it once in
a while, depending on how your date ranges move.

Otherwise, you can use some VBA programming, and a temp table to sorta
gerrymandering of the data and the report label captions. I won't bore you
with the details here, but you can ping me if you want to know more.

 

[Van T. Dinh]

I normally use a calculated Field formatting the date to the format
"yyyy/mm" so I end up with:

"2004/01", "2004/02", "2004/12", "2005/01", "2005/02" ....

and the Cross-tab Query will sort the Columns correctly for you.