Sorting by Month field in simple query

  • Thread starter Thread starter NEHicks
  • Start date Start date
N

NEHicks

I have a simple query that has individual fields for month, day and year.
The month field is a text field (Jan, Feb, etc.) and the day and year are
number fields.

When I run the query to give me all data in a specific year, I would like to
sort the months in chronological order. All I get is alphabetical order.

How do I do this?
 
You can create a calculated field along the lines of:

MonthNumber: Month(CDate([DayField] & "-" & [MonthField] & "-" &
[YearField]))

and sort on that field.
 
If you have the full date field available then add a column in your query
like this
and sort by it. (You don't have to show this column in your query)

WhichMonth: Month([FullDateField])

If you do not have the full date field available then create a columnin your
query like this and sort by it.

WhichMonth: Month(CDate("01 " & [MonthFieldName] & " 2009"))
 
You should have used a single date field. You could parse it out as needed.
Best would be to convert to that.

There are a number of ways you could manipulate the existing data. One
option is something like this as a new column in query desgin view:

FullDate: Val(Format(CDate("1-" & [TestMonth] & "-2000"),"m"))

Sort the column ascending. See Help for more information about Val, Format,
and CDate.

In a report you could group by the month field.

Another option may be to store the month number rather than text. You could
have the user select the month from a combo box that has the month number as
the bound column.

Yet another option may be to use the Switch function. See Help for more
information.

Again, these are workarounds for a situation that could be difficult to
manage over time.
 
The option Douglas suggested is better than mine in that it uses fewer
steps. The Month function returns a number that can be used for sorting
without further manipulation. Note that you can use any day and year since
you are only looking for the month. Note too that Douglas's suggestion
without the Month function can be used to assemble the three fields into
something Access will recognize as a date.

BruceM said:
You should have used a single date field. You could parse it out as
needed. Best would be to convert to that.

There are a number of ways you could manipulate the existing data. One
option is something like this as a new column in query desgin view:

FullDate: Val(Format(CDate("1-" & [TestMonth] & "-2000"),"m"))

Sort the column ascending. See Help for more information about Val,
Format, and CDate.

In a report you could group by the month field.

Another option may be to store the month number rather than text. You
could have the user select the month from a combo box that has the month
number as the bound column.

Yet another option may be to use the Switch function. See Help for more
information.

Again, these are workarounds for a situation that could be difficult to
manage over time.

NEHicks said:
I have a simple query that has individual fields for month, day and year.
The month field is a text field (Jan, Feb, etc.) and the day and year are
number fields.

When I run the query to give me all data in a specific year, I would like
to
sort the months in chronological order. All I get is alphabetical order.

How do I do this?
Click to expand...
Click to expand...
 
Back
Top