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]
