sorting a MONTH field in a cross-tab Query correctly

[Matt D Francis]

Hello,

I have a field ACTION_DT_TIME which stores a datetime value as follows

200605151507

=YYYYMMDDHHSS

I need to be able to pull out just the Month from this string, which I've
done with the following expression

ACTION_MONTH:
Format(DateSerial(Left([ACTION_DT_TM],4),Mid([ACTION_DT_TM],5,2),Mid([ACTION_DT_TM],7,2)),"Mmm")

which works in so far as it returns Janm, Feb, Mar etc

However I'm using this month field in a crosstab quary and the months are
sorted alphabetically rather than chronologically.

Apr Feb Jan Mar

Is there a way round this?

Cheers,

 

[Allen Browne]

You can specify the sort order if you list all the values in the Column
Headings property of the crosstab (Properties box, in query design view.)

But the better solution would be to use the month number rather than name,
i.e.:
Month([SomeDateField])

 

[Matt D Francis]

Wow, quick respose, thank-you!

Listing the fields in the order I wanted works. Well nearly. I've typed in

"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"

But for some reason the table of results lists them all in the correct order
except May which is coming up as the first column. Weird.
I only have data in the table for Jan-Apr at the moment - would that make a
difference?

You can specify the sort order if you list all the values in the Column
Headings property of the crosstab (Properties box, in query design view.)

But the better solution would be to use the month number rather than name,
i.e.:
Month([SomeDateField])

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hello,

I have a field ACTION_DT_TIME which stores a datetime value as follows

200605151507

=YYYYMMDDHHSS

I need to be able to pull out just the Month from this string, which I've
done with the following expression

ACTION_MONTH:
Format(DateSerial(Left([ACTION_DT_TM],4),Mid([ACTION_DT_TM],5,2),Mid([ACTION_DT_TM],7,2)),"Mmm")

which works in so far as it returns Janm, Feb, Mar etc

However I'm using this month field in a crosstab quary and the months are
sorted alphabetically rather than chronologically.

Apr Feb Jan Mar

 

[Allen Browne]

The fact that there is no data for May - Dec should not cause Access to
disregard the Column Headings setting.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Wow, quick respose, thank-you!

Listing the fields in the order I wanted works. Well nearly. I've typed in

"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"

But for some reason the table of results lists them all in the correct
order
except May which is coming up as the first column. Weird.
I only have data in the table for Jan-Apr at the moment - would that make
a
difference?

You can specify the sort order if you list all the values in the Column
Headings property of the crosstab (Properties box, in query design view.)

But the better solution would be to use the month number rather than
name,
i.e.:
Month([SomeDateField])

message

Hello,

I have a field ACTION_DT_TIME which stores a datetime value as follows

200605151507

=YYYYMMDDHHSS

I need to be able to pull out just the Month from this string, which
I've
done with the following expression

ACTION_MONTH:
Format(DateSerial(Left([ACTION_DT_TM],4),Mid([ACTION_DT_TM],5,2),Mid([ACTION_DT_TM],7,2)),"Mmm")

which works in so far as it returns Janm, Feb, Mar etc

However I'm using this month field in a crosstab quary and the months
are
sorted alphabetically rather than chronologically.

Apr Feb Jan Mar

 

[John Spencer]

Please copy and post the SQL of your query.

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message

Wow, quick respose, thank-you!

Listing the fields in the order I wanted works. Well nearly. I've typed in

"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"

But for some reason the table of results lists them all in the correct
order
except May which is coming up as the first column. Weird.
I only have data in the table for Jan-Apr at the moment - would that make
a
difference?

You can specify the sort order if you list all the values in the Column
Headings property of the crosstab (Properties box, in query design view.)

But the better solution would be to use the month number rather than
name,
i.e.:
Month([SomeDateField])

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message

Hello,

I have a field ACTION_DT_TIME which stores a datetime value as follows

200605151507

=YYYYMMDDHHSS

I need to be able to pull out just the Month from this string, which
I've
done with the following expression

ACTION_MONTH:
Format(DateSerial(Left([ACTION_DT_TM],4),Mid([ACTION_DT_TM],5,2),Mid([ACTION_DT_TM],7,2)),"Mmm")

which works in so far as it returns Janm, Feb, Mar etc

However I'm using this month field in a crosstab quary and the months
are
sorted alphabetically rather than chronologically.

Apr Feb Jan Mar

 

[Matt D Francis]

Sure:

TRANSFORM Count(EBSX02_2006.UBRN_ID) AS CountOfUBRN_ID
SELECT EBSX02_2006.ACTION_CD
FROM EBSX02_2006 INNER JOIN QRY_DATE_CONVERSIONS ON
(EBSX02_2006.ACTION_DT_TM = QRY_DATE_CONVERSIONS.ACTION_DT_TM) AND
(EBSX02_2006.UBRN_ID = QRY_DATE_CONVERSIONS.UBRN_ID) AND
(EBSX02_2006.ACTION_CD = QRY_DATE_CONVERSIONS.ACTION_CD)
GROUP BY EBSX02_2006.ACTION_CD
PIVOT QRY_DATE_CONVERSIONS.ACTION_MONTH In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");


The QRY_DATE_CONVERSIONS is a Query to generate fields extracting the
different elements of a date/time field.

Please copy and post the SQL of your query.

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message

Wow, quick respose, thank-you!

Listing the fields in the order I wanted works. Well nearly. I've typed in

"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"

But for some reason the table of results lists them all in the correct
order
except May which is coming up as the first column. Weird.
I only have data in the table for Jan-Apr at the moment - would that make
a
difference?

You can specify the sort order if you list all the values in the Column
Headings property of the crosstab (Properties box, in query design view.)

But the better solution would be to use the month number rather than
name,
i.e.:
Month([SomeDateField])

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message
Hello,

I have a field ACTION_DT_TIME which stores a datetime value as follows

200605151507

=YYYYMMDDHHSS

I need to be able to pull out just the Month from this string, which
I've
done with the following expression

ACTION_MONTH:
Format(DateSerial(Left([ACTION_DT_TM],4),Mid([ACTION_DT_TM],5,2),Mid([ACTION_DT_TM],7,2)),"Mmm")

which works in so far as it returns Janm, Feb, Mar etc

However I'm using this month field in a crosstab quary and the months
are
sorted alphabetically rather than chronologically.

Apr Feb Jan Mar

 

[John Spencer]

Maybe Allen Browne will see something in the query that would cause this. I
don't see anything that would cause "May" to appear as the first month
column. As an experiment I might try creating a new query and pasting this
text into the new query.

Sure:

TRANSFORM Count(EBSX02_2006.UBRN_ID) AS CountOfUBRN_ID
SELECT EBSX02_2006.ACTION_CD
FROM EBSX02_2006 INNER JOIN QRY_DATE_CONVERSIONS ON
(EBSX02_2006.ACTION_DT_TM = QRY_DATE_CONVERSIONS.ACTION_DT_TM) AND
(EBSX02_2006.UBRN_ID = QRY_DATE_CONVERSIONS.UBRN_ID) AND
(EBSX02_2006.ACTION_CD = QRY_DATE_CONVERSIONS.ACTION_CD)
GROUP BY EBSX02_2006.ACTION_CD
PIVOT QRY_DATE_CONVERSIONS.ACTION_MONTH In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");


The QRY_DATE_CONVERSIONS is a Query to generate fields extracting the
different elements of a date/time field.

Please copy and post the SQL of your query.

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message


message

Wow, quick respose, thank-you!

Listing the fields in the order I wanted works. Well nearly. I've typed
in

"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"

But for some reason the table of results lists them all in the correct
order
except May which is coming up as the first column. Weird.
I only have data in the table for Jan-Apr at the moment - would that
make
a
difference?


:

You can specify the sort order if you list all the values in the
Column
Headings property of the crosstab (Properties box, in query design
view.)

But the better solution would be to use the month number rather than
name,
i.e.:
Month([SomeDateField])

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message
Hello,

I have a field ACTION_DT_TIME which stores a datetime value as
follows

200605151507

=YYYYMMDDHHSS

I need to be able to pull out just the Month from this string, which
I've
done with the following expression

ACTION_MONTH:
Format(DateSerial(Left([ACTION_DT_TM],4),Mid([ACTION_DT_TM],5,2),Mid([ACTION_DT_TM],7,2)),"Mmm")

which works in so far as it returns Janm, Feb, Mar etc

However I'm using this month field in a crosstab quary and the
months
are
sorted alphabetically rather than chronologically.

Apr Feb Jan Mar

 

[Matt D Francis]

OK well thanks for trying anyway. Just a bug maybe..

Maybe Allen Browne will see something in the query that would cause this. I
don't see anything that would cause "May" to appear as the first month
column. As an experiment I might try creating a new query and pasting this
text into the new query.

Sure:

TRANSFORM Count(EBSX02_2006.UBRN_ID) AS CountOfUBRN_ID
SELECT EBSX02_2006.ACTION_CD
FROM EBSX02_2006 INNER JOIN QRY_DATE_CONVERSIONS ON
(EBSX02_2006.ACTION_DT_TM = QRY_DATE_CONVERSIONS.ACTION_DT_TM) AND
(EBSX02_2006.UBRN_ID = QRY_DATE_CONVERSIONS.UBRN_ID) AND
(EBSX02_2006.ACTION_CD = QRY_DATE_CONVERSIONS.ACTION_CD)
GROUP BY EBSX02_2006.ACTION_CD
PIVOT QRY_DATE_CONVERSIONS.ACTION_MONTH In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");


The QRY_DATE_CONVERSIONS is a Query to generate fields extracting the
different elements of a date/time field.

Please copy and post the SQL of your query.

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message


message
Wow, quick respose, thank-you!

Listing the fields in the order I wanted works. Well nearly. I've typed
in

"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"

But for some reason the table of results lists them all in the correct
order
except May which is coming up as the first column. Weird.
I only have data in the table for Jan-Apr at the moment - would that
make
a
difference?


:

You can specify the sort order if you list all the values in the
Column
Headings property of the crosstab (Properties box, in query design
view.)

But the better solution would be to use the month number rather than
name,
i.e.:
Month([SomeDateField])

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message
Hello,

I have a field ACTION_DT_TIME which stores a datetime value as
follows

200605151507

=YYYYMMDDHHSS

I need to be able to pull out just the Month from this string, which
I've
done with the following expression

ACTION_MONTH:
Format(DateSerial(Left([ACTION_DT_TM],4),Mid([ACTION_DT_TM],5,2),Mid([ACTION_DT_TM],7,2)),"Mmm")

which works in so far as it returns Janm, Feb, Mar etc

However I'm using this month field in a crosstab quary and the
months
are
sorted alphabetically rather than chronologically.

Apr Feb Jan Mar

 

[JMalecha]

What field in the properties box do you type the values? I've tried
Description and Format and neither work.

You can specify the sort order if you list all the values in the Column
Headings property of the crosstab (Properties box, in query design view.)

But the better solution would be to use the month number rather than name,
i.e.:
Month([SomeDateField])

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hello,

I have a field ACTION_DT_TIME which stores a datetime value as follows

200605151507

=YYYYMMDDHHSS

I need to be able to pull out just the Month from this string, which I've
done with the following expression

ACTION_MONTH:
Format(DateSerial(Left([ACTION_DT_TM],4),Mid([ACTION_DT_TM],5,2),Mid([ACTION_DT_TM],7,2)),"Mmm")

which works in so far as it returns Janm, Feb, Mar etc

However I'm using this month field in a crosstab quary and the months are
sorted alphabetically rather than chronologically.

Apr Feb Jan Mar