Sorting totals by month - it's sorting alphabetically

[Ron in NYC]

I created a query that sums hours worked by month. When I sort it in the report, it sorts alphabetically instead of by date. How do I fix that? Thanks.:banghead:

 

[plog]

Sort it by the month number instead.

 

[Ron in NYC]

Thanks. It's not a "number." It's text (October 2016), based on a query summarizing data by month.

 

[Ron in NYC]

I made a work around: I created another table with all dates, plus a column that shows only the first of the month (e.g., 6/20/2015 in one field, has a 6/1/2015 in the other). I don't like this solution as I will have to maintain that table, but I can get the report out I need right now. Would like a permanent solution.

 

[MarkK]

based on a query summarizing data by month.

In this query, add a field that is the number of the month. Include that field in the report, and then your sort is trivial.

 

[Ron in NYC]

I add a field as ... Month:[what goes here?]

Thanks Mark for the help.

 

[MarkK]

How do you "summarize data by month" in your existing query? I would expect to see a date field on which you run the VBA.Month() function, and then GROUP BY that field, simillarly with the Year.

Field: | Month: Month([ItemDate])  | Year: Year([ItemDate])
Table: | YourTable                 | YourTable
Total: | Group By                  | Group By

How do you get the name of the month in your query?

 

[plog]

You need to fix your table. You should not be storing data in a text field that is not text. You should be storing that data in a date field. Since you are only concerned with Month/Year, I would use the first date of every month to represent your data:

"Sepetember 2015" = 9/1/2015
"January 2016" = 1/1/2016

When you do that you can easily sort and filter your data like you want. You also get access to all the Date functions of Access--you could group your data by Quarter, compare months in prior years to the current year, etc.

Store your data properly and it will pay dividends in less work in the future..

 

[Ron in NYC]

The raw data is a date field but not the 1st day of the month.

None of my dates are in text fields. The field ACCESS creates as the month/date field is a text field (or appears to be). It's the field that only sorts alphabetically.

When you say "I would use the first day of every month to represent your data" what do you mean? I have a table with many dates, that I want to summarize by month. How do I make a 6/20/2015 show up as a 6/1/2015?

 

[Ron in NYC]

Markk, I get the month/year using the query wizard.

 

[Ron in NYC]

How do you "summarize data by month" in your existing query? I would expect to see a date field on which you run the VBA.Month() function, and then GROUP BY that field, simillarly with the Year.

Field: | Month: Month([ItemDate])  | Year: Year([ItemDate])
Table: | YourTable                 | YourTable
Total: | Group By                  | Group By

How do you get the name of the month in your query?

This worked!!! Thanks.

 

[MarkK]

Yeah, you bet!

 

[Ron in NYC]

I ultimately made it work, by making the month one field and the year another. Then I sort first by year, then my month and they come out in order. Works fine and gets the right results.

When I join the two fields (e.g., 2015-6), Access again sees it as a text.

 

[Ron in NYC]

I got that to work earlier, but can't now.

I have a date filed named "Date." I want it to show just the numeric month.

Trying to follow the example table:

The field is named "Date"

I can't see what part of the statement is point to the field.

Should it say on the field line "[Date]:Month([Itemdate])? That gets and error.

 

[Ron in NYC]

Hey Markk... figured it out again... not very difficult... sheesh!

 

[plog]

Date is a poor choice for a field name because its a reserved word: https://support.microsoft.com/en-us/kb/286335

Instead you should prefix what the date represents (e.g. SalesDate, EnrollDate, etc.). When you use reserve words for names it makes coding just a little more difficult.

 

[Ron in NYC]

Definitely.

Thanks.