Crosstab Query - Column Heading Dates

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

Guest

I am running a crosstab query however the column heading with the dates is
not in order. Instead of 1-2005, 2-2005.etc, I am getting 1-2005, 1-2006,
1-2007, 2-2005.
How can I get the dates to run sequentially at the top? The SQL code that I
am using for the original query is: GROUP BY Format([Month],"mm-yyyy"),

Thanks.
 
Bob said:
I am running a crosstab query however the column heading with the dates is
not in order. Instead of 1-2005, 2-2005.etc, I am getting 1-2005, 1-2006,
1-2007, 2-2005.
How can I get the dates to run sequentially at the top? The SQL code that
I
am using for the original query is: GROUP BY Format([Month],"mm-yyyy"),

Thanks.
Click to expand...

Bob,

Your data is sorting in alphabetical order as the results of your format
function are returning a string.

Will the crosstab always contain the same month/year columns? If so you can
use the Column Headings property to explicitly declare the headings in the
correct order.

You could also reformat your date to yyyy-mm.

There are other solutions, which I will be happy to explain if these two are
insufficient.

Ed Metcalfe
 
I am having the same problem, but my dates will change and I cannot have them
yy/mm. Can you please list the other options

Ed Metcalfe said:
Bob said:
I am running a crosstab query however the column heading with the dates is
not in order. Instead of 1-2005, 2-2005.etc, I am getting 1-2005, 1-2006,
1-2007, 2-2005.
How can I get the dates to run sequentially at the top? The SQL code that
I
am using for the original query is: GROUP BY Format([Month],"mm-yyyy"),

Thanks.
Click to expand...

Bob,

Your data is sorting in alphabetical order as the results of your format
function are returning a string.

Will the crosstab always contain the same month/year columns? If so you can
use the Column Headings property to explicitly declare the headings in the
correct order.

You could also reformat your date to yyyy-mm.

There are other solutions, which I will be happy to explain if these two are
insufficient.

Ed Metcalfe
Click to expand...
 
I am having the same problem, but my dates will change and I cannot have them
yy/mm. Can you please list the other options

Ed Metcalfe said:
Bob said:
I am running a crosstab query however the column heading with the dates is
not in order. Instead of 1-2005, 2-2005.etc, I am getting 1-2005, 1-2006,
1-2007, 2-2005.
How can I get the dates to run sequentially at the top? The SQL code that
I
am using for the original query is: GROUP BY Format([Month],"mm-yyyy"),

Thanks.
Click to expand...

Bob,

Your data is sorting in alphabetical order as the results of your format
function are returning a string.

Will the crosstab always contain the same month/year columns? If so you can
use the Column Headings property to explicitly declare the headings in the
correct order.

You could also reformat your date to yyyy-mm.

There are other solutions, which I will be happy to explain if these two are
insufficient.

Ed Metcalfe
Click to expand...
 
Is the output going to a form or report? What type of options are you
looking for? Why can't you have them yy/mm?

If you are extra demanding about this, you may have to use some code to
build the SQL statement in code with the Column Headings included.

Duane Hookom
MS Access MVP

JMalecha said:
I am having the same problem, but my dates will change and I cannot have
them
yy/mm. Can you please list the other options

Ed Metcalfe said:
Bob said:
I am running a crosstab query however the column heading with the dates
is
not in order. Instead of 1-2005, 2-2005.etc, I am getting 1-2005,
1-2006,
1-2007, 2-2005.
How can I get the dates to run sequentially at the top? The SQL code
that
I
am using for the original query is: GROUP BY Format([Month],"mm-yyyy"),

Thanks.
Click to expand...

Bob,

Your data is sorting in alphabetical order as the results of your format
function are returning a string.

Will the crosstab always contain the same month/year columns? If so you
can
use the Column Headings property to explicitly declare the headings in
the
correct order.

You could also reformat your date to yyyy-mm.

There are other solutions, which I will be happy to explain if these two
are
insufficient.

Ed Metcalfe
Click to expand...
Click to expand...
 
Back
Top