Format command and order by month problem...

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

Ataru Morooka wrote:[color=blue]
> 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[/color]

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, 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 <helenwheelss@yahoo.com.au> wrote in message news:<414E8CCD.4020205@yahoo.com.au>...[color=blue]
> Ataru Morooka wrote:[color=green]
> > 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[/color]
>
> 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[/color]

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



atarumorooka@yahoo.com (Ataru Morooka) wrote in message news:<c92e8c28.0409200652.2dfc3cf0@posting.google. com>...[color=blue]
> 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 <helenwheelss@yahoo.com.au> wrote in message news:<414E8CCD.4020205@yahoo.com.au>...[color=green]
> > Ataru Morooka wrote:[color=darkred]
> > > 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[/color]
> >
> > 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[/color][/color]

Ataru Morooka wrote:[color=blue]
> 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
>
>[/color]


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!

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 <helenwheelss@yahoo.com.au> wrote in message news:<41514237.4040302@yahoo.com.au>...[color=blue]
> Ataru Morooka wrote:[color=green]
> > 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
> >
> >[/color]
>
>
> 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![/color]