Sorting by Month and Year

[Steve]

Hi all-

I've recently run into a problem and after looking at other entries on
the groups entitled "sorting by month/year" or something to that
effect, i find that i'm still at a loss.

my first problem was changing the 15 or so decimal places i had to just
2 decimal places; i did this and now that i did, my chart is now
sorting alphabetically rather than by the month/year that it was
sorting as before. i'm a little confused as to why it switched when all
i did was just change the format of the SUM.

here is my SQL statement:

SELECT (Format([ldate],"mmm"" '""yy")) AS Expr1,
Sum(Format([levelchg],"Fixed")) AS [Sum]
FROM yearlyChartQ
GROUP BY (Format([ldate],"mmm"" '""yy")),
(Year([ldate])*12+Month([ldate])-1);

thanks in advance for any help!

-steve-

 

[kingston via AccessMonster.com]

Using "mmm" will return an abbreviation for the month so it will be sorted
alphabetically. Use "mm" instead to return the two digit representation of
the month.

Hi all-

I've recently run into a problem and after looking at other entries on
the groups entitled "sorting by month/year" or something to that
effect, i find that i'm still at a loss.

my first problem was changing the 15 or so decimal places i had to just
2 decimal places; i did this and now that i did, my chart is now
sorting alphabetically rather than by the month/year that it was
sorting as before. i'm a little confused as to why it switched when all
i did was just change the format of the SUM.

here is my SQL statement:

SELECT (Format([ldate],"mmm"" '""yy")) AS Expr1,
Sum(Format([levelchg],"Fixed")) AS [Sum]
FROM yearlyChartQ
GROUP BY (Format([ldate],"mmm"" '""yy")),
(Year([ldate])*12+Month([ldate])-1);

thanks in advance for any help!

-steve-

 

[KARL DEWEY]

Try formating after summing like this --
Format(Sum([levelchg]),"Fixed"))AS [Sum]

 

[John Vinson]

I've recently run into a problem and after looking at other entries on
the groups entitled "sorting by month/year" or something to that
effect, i find that i'm still at a loss.

my first problem was changing the 15 or so decimal places i had to just
2 decimal places; i did this and now that i did, my chart is now
sorting alphabetically rather than by the month/year that it was
sorting as before. i'm a little confused as to why it switched when all
i did was just change the format of the SUM.

here is my SQL statement:

SELECT (Format([ldate],"mmm"" '""yy")) AS Expr1,
Sum(Format([levelchg],"Fixed")) AS [Sum]
FROM yearlyChartQ
GROUP BY (Format([ldate],"mmm"" '""yy")),
(Year([ldate])*12+Month([ldate])-1);

The Format function returns a text string. The string "Apr 06" sorts
before the string "Mar 06".

Just group by [ldate] and use Expr1 - renamed for readability - as an
expression for display purposes; or, if you want to group all records
in a given month together, Group By the year and month. Also be aware
that you want to format your sums AFTER adding them - using Format
first will convert the value to a text string, and Access will just
have to convert it back to a number to sum it (or pop an error). I'd
also avoid using the reserved word Sum as a fieldname. Try

SELECT (Format([ldate],"mmm"" '""yy")) AS TheMonth,
Format(Sum([levelchg]),"Fixed")) AS [TheSum]
FROM yearlyChartQ
GROUP BY Year([LDate]), Month([LDate]);

John W. Vinson[MVP]



John W. Vinson[MVP]

 

[Steve]

Thanks John-

However, now i'm getting this error while trying to institute that SQL
code:

"You tried to execute a query that does not include the specified
expression 'Format([LDate],"mmm"" '""yy")' as part of an aggregate
function."

Thanks in advance

I've recently run into a problem and after looking at other entries on
the groups entitled "sorting by month/year" or something to that
effect, i find that i'm still at a loss.

my first problem was changing the 15 or so decimal places i had to just
2 decimal places; i did this and now that i did, my chart is now
sorting alphabetically rather than by the month/year that it was
sorting as before. i'm a little confused as to why it switched when all
i did was just change the format of the SUM.

here is my SQL statement:

SELECT (Format([ldate],"mmm"" '""yy")) AS Expr1,
Sum(Format([levelchg],"Fixed")) AS [Sum]
FROM yearlyChartQ
GROUP BY (Format([ldate],"mmm"" '""yy")),
(Year([ldate])*12+Month([ldate])-1);The Format function returns a text string. The string "Apr 06" sorts

before the string "Mar 06".

Just group by [ldate] and use Expr1 - renamed for readability - as an
expression for display purposes; or, if you want to group all records
in a given month together, Group By the year and month. Also be aware
that you want to format your sums AFTER adding them - using Format
first will convert the value to a text string, and Access will just
have to convert it back to a number to sum it (or pop an error). I'd
also avoid using the reserved word Sum as a fieldname. Try

SELECT (Format([ldate],"mmm"" '""yy")) AS TheMonth,
Format(Sum([levelchg]),"Fixed")) AS [TheSum]
FROM yearlyChartQ
GROUP BY Year([LDate]), Month([LDate]);

John W. Vinson[MVP]

John W. Vinson[MVP] - Hide quoted text -- Show quoted text -

 

[Steve]

Actually, now that I'm thinking about it, I really don't need to sort
by year, since the chart i'm doing is just for a one-year span.

Thanks John-

However, now i'm getting this error while trying to institute that SQL
code:

"You tried to execute a query that does not include the specified
expression 'Format([LDate],"mmm"" '""yy")' as part of an aggregate
function."

Thanks in advance

I've recently run into a problem and after looking at other entries on
the groups entitled "sorting by month/year" or something to that
effect, i find that i'm still at a loss.
my first problem was changing the 15 or so decimal places i had to just
2 decimal places; i did this and now that i did, my chart is now
sorting alphabetically rather than by the month/year that it was
sorting as before. i'm a little confused as to why it switched when all
i did was just change the format of the SUM.
here is my SQL statement:
SELECT (Format([ldate],"mmm"" '""yy")) AS Expr1,
Sum(Format([levelchg],"Fixed")) AS [Sum]
FROM yearlyChartQ
GROUP BY (Format([ldate],"mmm"" '""yy")),
(Year([ldate])*12+Month([ldate])-1);The Format function returns a text string. The string "Apr 06" sorts

before the string "Mar 06".

Just group by [ldate] and use Expr1 - renamed for readability - as an
expression for display purposes; or, if you want to group all records
in a given month together, Group By the year and month. Also be aware
that you want to format your sums AFTER adding them - using Format
first will convert the value to a text string, and Access will just
have to convert it back to a number to sum it (or pop an error). I'd
also avoid using the reserved word Sum as a fieldname. Try

SELECT (Format([ldate],"mmm"" '""yy")) AS TheMonth,
Format(Sum([levelchg]),"Fixed")) AS [TheSum]
FROM yearlyChartQ
GROUP BY Year([LDate]), Month([LDate]);

John W. Vinson[MVP]

John W. Vinson[MVP] - Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -