Non-date field sorting by correct month order

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

JoeIBM - 26 Apr 2006 19:05 GMT

I'm pretty new to access, but not so new to DB's. Alas - I'm slightly
more proficient with SQL than with Access 2003's GUI tools like
expression builder, and specific microsoft things like VB Script.

</preamble>

My issue: I have a field that's a text field, but stores month names.

I'm trying to run a report that breaks down 5 years into totals by
month, and I'd like to sort the months in temporal order rather than
alphabetical order.

Any suggestions on how to do this, given the Month field isn't a
date/time formatted field?

The output is a text box, embedded in a report, with the control source
set as the Month field. Just wondering if I can set an order to those
months manually or through some sort of query?

Reply to this Message

Klatuu - 26 Apr 2006 20:42 GMT

Access date handling functions are pretty flexible. Most can handle almost
anything that looks like a date. So, if you want to take April and turn it
into it's numeric equivilante take the month field, add some characters in
front of and behind it and use the Month function.
For example, let's say MONTH_FIELD contains "April"

intMonthNo = Month("1-" & MONTH_FIELD & "-2000")
intMonthNo will now contain the number 3

Above would be assigning it to a variable. If you want to use it in a query

SELECT Month("1-" & MONTH_FIELD & "-2000") AS TheMonth, ....

Now, if you are using the Query Builder, rather than use the field name
directly in the field row, give it an alias:
TheMonth: Month("1-" & MONTH_FIELD & "-2000")
If you switch to SQL view in the builder, it will look like it does in the
SELECT statment.

If you want to create saved queries, but are more comfortable writing your
own SQL, rather than design view, switch to SQL view and write your code.

Access saves all queries as SQL strings, anyway. The builder just parses
them for the GUI.

> I'm pretty new to access, but not so new to DB's. Alas - I'm slightly
> more proficient with SQL than with Access 2003's GUI tools like
[quoted text clipped - 14 lines]
> set as the Month field. Just wondering if I can set an order to those
> months manually or through some sort of query?

Reply to this Message