Calendar sorting date field formatted to "mmm"

[coastal]

I have the following date field
Months: Format([Month],"mmm")

The above field when sorted asscending sorts in Alpha order, but I need it
sort it by Calendar order, Any suggestions?

 

[Tom Ellison]

Dear Coastal:

If the numeric value of the month is in Month, then sort by that. Add Month
as a column to the query but do not display it (unless you want to).

Tom Ellison

 

[Douglas J. Steele]

Sorry to butt in, Tom, but if the Format function is working on the [Month]
field, that would imply that [Month] is an actual date field. That implies
that another computed field SortMonth: Month([Month]) or SortMonth:
DatePart("m", [Month]) should be added to the query for sort purposes.

coastal: please reconsider renaming your field. Month is a reserved word, so
you shouldn't use it as a field name.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Dear Coastal:

If the numeric value of the month is in Month, then sort by that. Add
Month as a column to the query but do not display it (unless you want to).

Tom Ellison

I have the following date field
Months: Format([Month],"mmm")

The above field when sorted asscending sorts in Alpha order, but I need
it
sort it by Calendar order, Any suggestions?

 

[Tom Ellison]

Hey Doug,

Sure, but if it is a date, then it includes a year component. Thus, January
last year comes 12 months before January this year. That could be a good
thing. The date component would further order things, but not in a way that
would be at all harmful. Even a time component wouldn't cause a problem for
sorting purposes.

Whether the poster wants all the January dates for the past 10 years
together, followed by all the Februaries, and so forth, is not clear. That
would be, in my opinion, very unusual. So, sorting the way I suggested
would be, in my opinion, more likely to be what is wanted.

The real answer is not clear from what has been posted. Facts not in
evidence.

Tom Ellison

Sorry to butt in, Tom, but if the Format function is working on the
[Month] field, that would imply that [Month] is an actual date field. That
implies that another computed field SortMonth: Month([Month]) or
SortMonth: DatePart("m", [Month]) should be added to the query for sort
purposes.

coastal: please reconsider renaming your field. Month is a reserved word,
so you shouldn't use it as a field name.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Dear Coastal:

If the numeric value of the month is in Month, then sort by that. Add
Month as a column to the query but do not display it (unless you want
to).

Tom Ellison

I have the following date field
Months: Format([Month],"mmm")

The above field when sorted asscending sorts in Alpha order, but I need
it
sort it by Calendar order, Any suggestions?

 

[coastal]

Right not enough info.... here is hopefully more to the point:

The field is in a query that is the base of a chart. The line chart has the
months on the x axis and the years as series, thus plotting the
increase/decrease of product users over time.

For the chart: what I really need is to have the months/x-axis in "mmm"
format and in calendar year sorting.

Same thing for Crosstab queries. Need to have year in the rows, and months
as columns sorted in calendar year and formatted to "mmm".


--
--coastal

Hey Doug,

Sure, but if it is a date, then it includes a year component. Thus, January
last year comes 12 months before January this year. That could be a good
thing. The date component would further order things, but not in a way that
would be at all harmful. Even a time component wouldn't cause a problem for
sorting purposes.

Whether the poster wants all the January dates for the past 10 years
together, followed by all the Februaries, and so forth, is not clear. That
would be, in my opinion, very unusual. So, sorting the way I suggested
would be, in my opinion, more likely to be what is wanted.

The real answer is not clear from what has been posted. Facts not in
evidence.

Tom Ellison

Sorry to butt in, Tom, but if the Format function is working on the
[Month] field, that would imply that [Month] is an actual date field. That
implies that another computed field SortMonth: Month([Month]) or
SortMonth: DatePart("m", [Month]) should be added to the query for sort
purposes.

coastal: please reconsider renaming your field. Month is a reserved word,
so you shouldn't use it as a field name.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Dear Coastal:

If the numeric value of the month is in Month, then sort by that. Add
Month as a column to the query but do not display it (unless you want
to).

Tom Ellison



I have the following date field
Months: Format([Month],"mmm")

The above field when sorted asscending sorts in Alpha order, but I need
it
sort it by Calendar order, Any suggestions?

 

[Douglas J. Steele]

As Tom said, you need to add an additional column to your query for sort
purposes.

The only issue is what that field should be. What is [Month] in your field
Format([Month],"mmm")? Assuming it's a date, then the additional field you
need is Month([Month]). If it's actually a month number, that's the
additional field you need.


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Right not enough info.... here is hopefully more to the point:

The field is in a query that is the base of a chart. The line chart has
the
months on the x axis and the years as series, thus plotting the
increase/decrease of product users over time.

For the chart: what I really need is to have the months/x-axis in "mmm"
format and in calendar year sorting.

Same thing for Crosstab queries. Need to have year in the rows, and
months
as columns sorted in calendar year and formatted to "mmm".


--
--coastal

Hey Doug,

Sure, but if it is a date, then it includes a year component. Thus,
January
last year comes 12 months before January this year. That could be a good
thing. The date component would further order things, but not in a way
that
would be at all harmful. Even a time component wouldn't cause a problem
for
sorting purposes.

Whether the poster wants all the January dates for the past 10 years
together, followed by all the Februaries, and so forth, is not clear.
That
would be, in my opinion, very unusual. So, sorting the way I suggested
would be, in my opinion, more likely to be what is wanted.

The real answer is not clear from what has been posted. Facts not in
evidence.

Tom Ellison

Sorry to butt in, Tom, but if the Format function is working on the
[Month] field, that would imply that [Month] is an actual date field.
That
implies that another computed field SortMonth: Month([Month]) or
SortMonth: DatePart("m", [Month]) should be added to the query for sort
purposes.

coastal: please reconsider renaming your field. Month is a reserved
word,
so you shouldn't use it as a field name.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Dear Coastal:

If the numeric value of the month is in Month, then sort by that. Add
Month as a column to the query but do not display it (unless you want
to).

Tom Ellison



I have the following date field
Months: Format([Month],"mmm")

The above field when sorted asscending sorts in Alpha order, but I
need
it
sort it by Calendar order, Any suggestions?

 

[coastal]

Thanks...yes the field is a date.

I have been able to get the query to sort properly...the problem is in the
chart that is based on the query. Even with a properly sorted query I have
not been able to translate that sorting into the chart.

I have the month on the x-axis and the year in the series thus plotting
product users per year per month.

I have tried both fields (formatted in query as "mmm" and the one formatted
as Month([Month]) in the x-axis.
"mmm" sorts in alpha order but gives me the formatting I want users to see
the second gives me the sorting needed by not the proper formatting.

Lost in space.
--
--coastal

As Tom said, you need to add an additional column to your query for sort
purposes.

The only issue is what that field should be. What is [Month] in your field
Format([Month],"mmm")? Assuming it's a date, then the additional field you
need is Month([Month]). If it's actually a month number, that's the
additional field you need.


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Right not enough info.... here is hopefully more to the point:

The field is in a query that is the base of a chart. The line chart has
the
months on the x axis and the years as series, thus plotting the
increase/decrease of product users over time.

For the chart: what I really need is to have the months/x-axis in "mmm"
format and in calendar year sorting.

Same thing for Crosstab queries. Need to have year in the rows, and
months
as columns sorted in calendar year and formatted to "mmm".


--
--coastal

Hey Doug,

Sure, but if it is a date, then it includes a year component. Thus,
January
last year comes 12 months before January this year. That could be a good
thing. The date component would further order things, but not in a way
that
would be at all harmful. Even a time component wouldn't cause a problem
for
sorting purposes.

Whether the poster wants all the January dates for the past 10 years
together, followed by all the Februaries, and so forth, is not clear.
That
would be, in my opinion, very unusual. So, sorting the way I suggested
would be, in my opinion, more likely to be what is wanted.

The real answer is not clear from what has been posted. Facts not in
evidence.

Tom Ellison


Sorry to butt in, Tom, but if the Format function is working on the
[Month] field, that would imply that [Month] is an actual date field.
That
implies that another computed field SortMonth: Month([Month]) or
SortMonth: DatePart("m", [Month]) should be added to the query for sort
purposes.

coastal: please reconsider renaming your field. Month is a reserved
word,
so you shouldn't use it as a field name.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Dear Coastal:

If the numeric value of the month is in Month, then sort by that. Add
Month as a column to the query but do not display it (unless you want
to).

Tom Ellison



I have the following date field
Months: Format([Month],"mmm")

The above field when sorted asscending sorts in Alpha order, but I
need
it
sort it by Calendar order, Any suggestions?