Sorting Month-Year in Query

[Pinacle]

I have a field in my table which holds month/year (Ex: jan-10) I am trying to
sort this field in ascending order. But when i sort the column it is sorting
in alphabetical order(Apr,Feb, Jan...) instead of sorting by Jan, Feb , Mar
and so on.

Purpose of my query is... i'm calculating running total of the sales revenue
for all the months.

 

[Duane Hookom]

You should be storing a real date value or numeric month and year vlaue(s).
If you can't change the field, then hopefully you have been consistent with
your values so you can create a date.
For instance, you can add "28-" & to your field value and then use
DateValue() to get a real date that can be sorted correctly.

RealDate: DateValue("28-" & [Some Field in your table])

 

[Arvin Meyer [MVP]]

You cannot sort a text field like a date. Jan-10 is not a date. A date has a
month, a day, and a year, not a month and year. You have 2 options:

1. Add a sort field

2. Change the field to a date (1/1/10) and sort on the date, then use a
query to add a column:

SalesMonth: Format([MyDateField], "mmm" & "-" & "yy")

 

[Pinacle]

Thanks for the solution Mr.Hookom.

You should be storing a real date value or numeric month and year vlaue(s).
If you can't change the field, then hopefully you have been consistent with
your values so you can create a date.
For instance, you can add "28-" & to your field value and then use
DateValue() to get a real date that can be sorted correctly.

RealDate: DateValue("28-" & [Some Field in your table])

--
Duane Hookom
Microsoft Access MVP

I have a field in my table which holds month/year (Ex: jan-10) I am trying to
sort this field in ascending order. But when i sort the column it is sorting
in alphabetical order(Apr,Feb, Jan...) instead of sorting by Jan, Feb , Mar
and so on.

Purpose of my query is... i'm calculating running total of the sales revenue
for all the months.

 

[Aria]

I'm having the same problem. I want to sort a report by month and am using
group by in the query which includes this as a calculated field:

RqstMonth: Format$([RqstDate],"mmmm")

On second thought here's the SQL for the query:

SELECT tblKeysRequests.EmpID, tblEmployees.EmployeeType, [FirstName] & " " &
[LastName] AS FullName, tblEmployees.FirstName, tblEmployees.LastName,
tblKeys.KeyID, tblKeys.KeyCode, tblKeysRequests.RqstDate,
tblKeysRequests.QtyRequested, tblKeysRequests.RcvdDate,
tblKeysRequests.QtyRecd, tblKeysRequests.Reason, Format$([RqstDate],"mmmm")
AS RqstMonth
FROM tblKeys INNER JOIN (tblEmployees INNER JOIN tblKeysRequests ON
tblEmployees.EmpID = tblKeysRequests.EmpID) ON tblKeys.KeyID =
tblKeysRequests.KeyID
GROUP BY tblKeysRequests.EmpID, tblEmployees.EmployeeType, [FirstName] & " "
& [LastName], tblEmployees.FirstName, tblEmployees.LastName, tblKeys.KeyID,
tblKeys.KeyCode, tblKeysRequests.RqstDate, tblKeysRequests.QtyRequested,
tblKeysRequests.RcvdDate, tblKeysRequests.QtyRecd, tblKeysRequests.Reason,
Format$([RqstDate],"mmmm")
HAVING (((tblEmployees.EmployeeType)<>2) AND (Not (tblKeysRequests.RcvdDate)
Is Null))
ORDER BY tblEmployees.LastName, tblKeys.KeyCode;

Everything is displayed as I'd like except I'm having a problem putting the
months in calendar order. Any suggestions?
--
Aria W.

You cannot sort a text field like a date. Jan-10 is not a date. A date has a
month, a day, and a year, not a month and year. You have 2 options:

1. Add a sort field

2. Change the field to a date (1/1/10) and sort on the date, then use a
query to add a column:

SalesMonth: Format([MyDateField], "mmm" & "-" & "yy")
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

I have a field in my table which holds month/year (Ex: jan-10) I am trying
to
sort this field in ascending order. But when i sort the column it is
sorting
in alphabetical order(Apr,Feb, Jan...) instead of sorting by Jan, Feb ,
Mar
and so on.

Purpose of my query is... i'm calculating running total of the sales
revenue
for all the months.

.

 

[John W. Vinson]

I'm having the same problem. I want to sort a report by month and am using
group by in the query which includes this as a calculated field:

RqstMonth: Format$([RqstDate],"mmmm")

This function returns a text string, not a date. The text string "April" sorts
before the text string "January".

On second thought here's the SQL for the query:

SELECT tblKeysRequests.EmpID, tblEmployees.EmployeeType, [FirstName] & " " &
[LastName] AS FullName, tblEmployees.FirstName, tblEmployees.LastName,
tblKeys.KeyID, tblKeys.KeyCode, tblKeysRequests.RqstDate,
tblKeysRequests.QtyRequested, tblKeysRequests.RcvdDate,
tblKeysRequests.QtyRecd, tblKeysRequests.Reason, Format$([RqstDate],"mmmm")
AS RqstMonth
FROM tblKeys INNER JOIN (tblEmployees INNER JOIN tblKeysRequests ON
tblEmployees.EmpID = tblKeysRequests.EmpID) ON tblKeys.KeyID =
tblKeysRequests.KeyID
GROUP BY tblKeysRequests.EmpID, tblEmployees.EmployeeType, [FirstName] & " "
& [LastName], tblEmployees.FirstName, tblEmployees.LastName, tblKeys.KeyID,
tblKeys.KeyCode, tblKeysRequests.RqstDate, tblKeysRequests.QtyRequested,
tblKeysRequests.RcvdDate, tblKeysRequests.QtyRecd, tblKeysRequests.Reason,
Format$([RqstDate],"mmmm")
HAVING (((tblEmployees.EmployeeType)<>2) AND (Not (tblKeysRequests.RcvdDate)
Is Null))
ORDER BY tblEmployees.LastName, tblKeys.KeyCode;

Everything is displayed as I'd like except I'm having a problem putting the
months in calendar order. Any suggestions?

Sort by date, not by the text name of the month:

ORDER BY tblEmployees.LastName, tblKeys.KEyCode, tblKeysRequests.RqstDate;

One question: you're using a GROUP BY but not doing any grouped operations
such as sums or totals. Are you sure you need this? You might want to try
unselecting the Totals operator on the query grid and see if you don't get the
same results... much faster!

 

[Duane Hookom]

It isn't clear how you want this sorted. Do you care only about the month
number or the month and year? How does this related to the order of LastName
and KeyCode?

If you have RqstDate, you can get its sortable month with the
Month(Rqstdate) function. Typically I don't do any formatting like this in
the query. Formatting of values is a visual/presentation task that should be
handled in the controls on your form or report.

--
Duane Hookom
Microsoft Access MVP

I'm having the same problem. I want to sort a report by month and am using
group by in the query which includes this as a calculated field:

RqstMonth: Format$([RqstDate],"mmmm")

On second thought here's the SQL for the query:

SELECT tblKeysRequests.EmpID, tblEmployees.EmployeeType, [FirstName] & " " &
[LastName] AS FullName, tblEmployees.FirstName, tblEmployees.LastName,
tblKeys.KeyID, tblKeys.KeyCode, tblKeysRequests.RqstDate,
tblKeysRequests.QtyRequested, tblKeysRequests.RcvdDate,
tblKeysRequests.QtyRecd, tblKeysRequests.Reason, Format$([RqstDate],"mmmm")
AS RqstMonth
FROM tblKeys INNER JOIN (tblEmployees INNER JOIN tblKeysRequests ON
tblEmployees.EmpID = tblKeysRequests.EmpID) ON tblKeys.KeyID =
tblKeysRequests.KeyID
GROUP BY tblKeysRequests.EmpID, tblEmployees.EmployeeType, [FirstName] & " "
& [LastName], tblEmployees.FirstName, tblEmployees.LastName, tblKeys.KeyID,
tblKeys.KeyCode, tblKeysRequests.RqstDate, tblKeysRequests.QtyRequested,
tblKeysRequests.RcvdDate, tblKeysRequests.QtyRecd, tblKeysRequests.Reason,
Format$([RqstDate],"mmmm")
HAVING (((tblEmployees.EmployeeType)<>2) AND (Not (tblKeysRequests.RcvdDate)
Is Null))
ORDER BY tblEmployees.LastName, tblKeys.KeyCode;

Everything is displayed as I'd like except I'm having a problem putting the
months in calendar order. Any suggestions?
--
Aria W.

You cannot sort a text field like a date. Jan-10 is not a date. A date has a
month, a day, and a year, not a month and year. You have 2 options:

1. Add a sort field

2. Change the field to a date (1/1/10) and sort on the date, then use a
query to add a column:

SalesMonth: Format([MyDateField], "mmm" & "-" & "yy")
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

I have a field in my table which holds month/year (Ex: jan-10) I am trying
to
sort this field in ascending order. But when i sort the column it is
sorting
in alphabetical order(Apr,Feb, Jan...) instead of sorting by Jan, Feb ,
Mar
and so on.

Purpose of my query is... i'm calculating running total of the sales
revenue
for all the months.

.

 

[Duane Hookom]

I'm not sure what you even want. If this is for a report, then the sorting in
the query doesn't have any effect on the results. I'm not sure why you are
doing any grouping in the query and grouping by fields that aren't in the
SELECT clause.

Please figure out exactly what type of output you want and where you want
it. Then come back with some good specifications so we can help.

--
Duane Hookom
Microsoft Access MVP

Do you care only about the monthnumber or the month and year? How does this related to the order >of LastName and KeyCode?

Oh gee...I didn't think about any of that. What I would like to see are the
requests ordered by month and key code. Normally, it's the code I'm trying to
find. Based on your questions, I changed the query. I took out the names so
that I wouldn't see repeating data. I moved the labels to the ReqstMonth
header.

Here's the new SQL:
SELECT tblKeysRequests.EmpID, tblEmployees.EmployeeType, tblKeys.KeyID,
tblKeys.KeyCode, tblKeysRequests.RqstDate, tblKeysRequests.QtyRequested,
tblKeysRequests.RcvdDate, tblKeysRequests.QtyRecd, tblKeysRequests.Reason,
Format$([RqstDate],"mmmm") AS RqstMonth
FROM tblKeys INNER JOIN (tblEmployees INNER JOIN tblKeysRequests ON
tblEmployees.EmpID = tblKeysRequests.EmpID) ON tblKeys.KeyID =
tblKeysRequests.KeyID
GROUP BY tblKeysRequests.EmpID, tblEmployees.EmployeeType, tblKeys.KeyID,
tblKeys.KeyCode, tblKeysRequests.RqstDate, tblKeysRequests.QtyRequested,
tblKeysRequests.RcvdDate, tblKeysRequests.QtyRecd, tblKeysRequests.Reason,
Format$([RqstDate],"mmmm"), [FirstName] & " " & [LastName], tblEmployees.
FirstName, tblEmployees.LastName
HAVING (((tblEmployees.EmployeeType)<>2) AND (Not (tblKeysRequests.RcvdDate)
Is Null))
ORDER BY tblKeys.KeyCode, tblKeysRequests.RqstDate;

Should I remove the names section completely? I hadn't come up with a
solution to get this to automatically roll over for the next school year. I
was getting to that after I get this worked out. Thanks to both of you for
your questions. I'm still a novice learning as I go.

It isn't clear how you want this sorted. Do you care only about the month
number or the month and year? How does this related to the order of LastName
and KeyCode?

If you have RqstDate, you can get its sortable month with the
Month(Rqstdate) function. Typically I don't do any formatting like this in
the query. Formatting of values is a visual/presentation task that should be
handled in the controls on your form or report.

I'm having the same problem. I want to sort a report by month and am using
group by in the query which includes this as a calculated field:

[quoted text clipped - 46 lines]

.