Format command and order by month problem...

[Ataru Morooka]

Hi, my table has to have a column with the months names (january,
february...).
When I order it by month it is ordered alphabetically and that's not
what I need.
Reading this ng I found someone suggesting to replace names with
numbers (datatype: number) so jan is 1, feb is 2 and so on...and then
to ma ke a query using the format command.

I wrote an example:
SELECT Format([Name],"mmmm") AS NameMonths
FROM Subscriptions
ORDER BY Format([Name],"mmmm");

I made the table and I wrote 3,2,1 as an example...and when I run the
query it writes December, January, January.
So, wrong months names...what's wrong?
Is it the right way to manage and solve my problem with ordering by
month?
Thank you,
Ataru Morooka

[Helen Wheels]

Ataru Morooka wrote:

Hi, my table has to have a column with the months names (january,
february...).
When I order it by month it is ordered alphabetically and that's not
what I need.
Reading this ng I found someone suggesting to replace names with
numbers (datatype: number) so jan is 1, feb is 2 and so on...and then
to ma ke a query using the format command.

I wrote an example:
SELECT Format([Name],"mmmm") AS NameMonths
FROM Subscriptions
ORDER BY Format([Name],"mmmm");

I made the table and I wrote 3,2,1 as an example...and when I run the
query it writes December, January, January.
So, wrong months names...what's wrong?
Is it the right way to manage and solve my problem with ordering by
month?
Thank you,
Ataru Morooka

The "mmmm" format is designed for date datatypes, not numbers.
Your solution would have worked if your [name] column had a date
datatype, and instead of values 3,2,1 you had entered (say)
25 mar 2004, 17 feb 2004, 10 jan 2004.

Since your [name] column has a number datatype, you'll need to
use a different function to convert numbers to month names for
display. A simple possibility is the Choose function (you'll find
more details in Access' help for that function):

SELECT Choose([name],"January","February","March","April","May",
"June","July","August","September","October","Nove mber","December")
AS NameMonths
FROM Subscriptions
ORDER BY [name];

The above assumes that all your users will be using English.
Here's an alternative that takes account of the user's language
preference by first converting [name] to a date (the 1st of
[name] 1901), then applying the "mmmm" format:

SELECT Format(DateSerial(1,[name],1),"mmmm") AS NameMonths
FROM Subscriptions
ORDER BY [name];

There are probably loads of other ways - these are just a start.

Cheers

[Ataru Morooka]

Helen, you are splendid...your help was needed.
The db will be in italian, so I'll just translate those names in italian.
Have a great day,
Ataru

Helen Wheels <he**********@yahoo.com.au> wrote in message news:<41**************@yahoo.com.au>...

Ataru Morooka wrote:

Hi, my table has to have a column with the months names (january,
february...).
When I order it by month it is ordered alphabetically and that's not
what I need.
Reading this ng I found someone suggesting to replace names with
numbers (datatype: number) so jan is 1, feb is 2 and so on...and then
to ma ke a query using the format command.

I wrote an example:
SELECT Format([Name],"mmmm") AS NameMonths
FROM Subscriptions
ORDER BY Format([Name],"mmmm");

I made the table and I wrote 3,2,1 as an example...and when I run the
query it writes December, January, January.
So, wrong months names...what's wrong?
Is it the right way to manage and solve my problem with ordering by
month?
Thank you,
Ataru Morooka

The "mmmm" format is designed for date datatypes, not numbers.
Your solution would have worked if your [name] column had a date
datatype, and instead of values 3,2,1 you had entered (say)
25 mar 2004, 17 feb 2004, 10 jan 2004.

Since your [name] column has a number datatype, you'll need to
use a different function to convert numbers to month names for
display. A simple possibility is the Choose function (you'll find
more details in Access' help for that function):

SELECT Choose([name],"January","February","March","April","May",
"June","July","August","September","October","Nove mber","December")
AS NameMonths
FROM Subscriptions
ORDER BY [name];

The above assumes that all your users will be using English.
Here's an alternative that takes account of the user's language
preference by first converting [name] to a date (the 1st of
[name] 1901), then applying the "mmmm" format:

SELECT Format(DateSerial(1,[name],1),"mmmm") AS NameMonths
FROM Subscriptions
ORDER BY [name];

There are probably loads of other ways - these are just a start.

Cheers

[Ataru Morooka]

Aaargh, here I am again...maybe Helen or someone else culd help.
As suggested I used the CHOOSE command to repleace a list of numbers
with the months names and then I ordered those months correctly.
NOW the problem is that when I create a REPORT on that query, the
report correctly shows the months names but they are not ordered
correctly....and...if I order them they are again ordered
alphabetically!!!
Any help....again?
Thank you,
Ataru

at**********@yahoo.com (Ataru Morooka) wrote in message news:<c9**************************@posting.google. com>...

Helen, you are splendid...your help was needed.
The db will be in italian, so I'll just translate those names in italian.
Have a great day,
Ataru

Helen Wheels <he**********@yahoo.com.au> wrote in message news:<41**************@yahoo.com.au>...

Ataru Morooka wrote:

Hi, my table has to have a column with the months names (january,
february...).
When I order it by month it is ordered alphabetically and that's not
what I need.
Reading this ng I found someone suggesting to replace names with
numbers (datatype: number) so jan is 1, feb is 2 and so on...and then
to ma ke a query using the format command.

I wrote an example:
SELECT Format([Name],"mmmm") AS NameMonths
FROM Subscriptions
ORDER BY Format([Name],"mmmm");

I made the table and I wrote 3,2,1 as an example...and when I run the
query it writes December, January, January.
So, wrong months names...what's wrong?
Is it the right way to manage and solve my problem with ordering by
month?
Thank you,
Ataru Morooka

The "mmmm" format is designed for date datatypes, not numbers.
Your solution would have worked if your [name] column had a date
datatype, and instead of values 3,2,1 you had entered (say)
25 mar 2004, 17 feb 2004, 10 jan 2004.

Since your [name] column has a number datatype, you'll need to
use a different function to convert numbers to month names for
display. A simple possibility is the Choose function (you'll find
more details in Access' help for that function):

SELECT Choose([name],"January","February","March","April","May",
"June","July","August","September","October","Nove mber","December")
AS NameMonths
FROM Subscriptions
ORDER BY [name];

The above assumes that all your users will be using English.
Here's an alternative that takes account of the user's language
preference by first converting [name] to a date (the 1st of
[name] 1901), then applying the "mmmm" format:

SELECT Format(DateSerial(1,[name],1),"mmmm") AS NameMonths
FROM Subscriptions
ORDER BY [name];

There are probably loads of other ways - these are just a start.

Cheers

[Helen Wheels]

Ataru Morooka wrote:

Aaargh, here I am again...maybe Helen or someone else culd help.
As suggested I used the CHOOSE command to repleace a list of numbers
with the months names and then I ordered those months correctly.
NOW the problem is that when I create a REPORT on that query, the
report correctly shows the months names but they are not ordered
correctly....and...if I order them they are again ordered
alphabetically!!!
Any help....again?
Thank you,
Ataru

An Access report usually ignores any sorting used in the report's
recordsource, so although your query is sorted OK, a report based
on it won't necessarily be OK too. To make sure the report is
sorted the way you want, open the report in design view and
select View/Sorting and grouping from the menu. In the sorting
and grouping popup, choose the field you want the report to sort
by - in your case you'll need to choose the field containing the
month number, not the month name.
Hope that helps!

[Ataru Morooka]

YES! In my original query I was having only the column with the months
names...I have added a column with the months numbers. Then I have
created the report and sorted it on the numbers (before I couldn't
because that column wasn't there, obviously!). On the page then I left
the months names and cancelled the numbers...and now it is perfect.
Really thank you,
Ataru
Helen Wheels <he**********@yahoo.com.au> wrote in message news:<41**************@yahoo.com.au>...

Ataru Morooka wrote:

Aaargh, here I am again...maybe Helen or someone else culd help.
As suggested I used the CHOOSE command to repleace a list of numbers
with the months names and then I ordered those months correctly.
NOW the problem is that when I create a REPORT on that query, the
report correctly shows the months names but they are not ordered
correctly....and...if I order them they are again ordered
alphabetically!!!
Any help....again?
Thank you,
Ataru

An Access report usually ignores any sorting used in the report's
recordsource, so although your query is sorted OK, a report based
on it won't necessarily be OK too. To make sure the report is
sorted the way you want, open the report in design view and
select View/Sorting and grouping from the menu. In the sorting
and grouping popup, choose the field you want the report to sort
by - in your case you'll need to choose the field containing the
month number, not the month name.
Hope that helps!