So easy but missing it.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a query that in order to sum up the Quantity based on Ship_Date I
added a field Ship Date:Format([Ship_Date],"mm-01-yy") this way when I create
my crosstab query off this one all data is rolled up and the column headings
are mm-01-yy (rolling 12 months).
Now the problem is that 01-01-05 comes before 04-01-04 due to the first 2
numbers. I feel like a dog chasing its tail, it can be accomplished but all
the routes I have taken are wrong.

Thanks! Stacey
 
SMac said:
I have a query that in order to sum up the Quantity based on
Ship_Date I added a field Ship Date:Format([Ship_Date],"mm-01-yy")
this way when I create my crosstab query off this one all data is
rolled up and the column headings are mm-01-yy (rolling 12 months).
Now the problem is that 01-01-05 comes before 04-01-04 due to the
first 2 numbers. I feel like a dog chasing its tail, it can be
accomplished but all the routes I have taken are wrong.

Thanks! Stacey
Click to expand...

A crosstab query has a "ColumnHeadings" property. If you enter your column
headings in that property (separated by columns), you can control the order.
It will also force columns to display even if the query has no data for that
column.
 
Consider creating columns of relative months using the DateAdd() function.
Your column headings expression could be something like:
ColHead: "M" & DateDiff("m", [Ship_Date], Date())
Then set the column headings property to:
"M11","M10",..."M0"
The M0 column will be this month and M11 will be 11 months ago.
 
Back
Top