General discussion

Locked

Access - sort by month

By susanakil ·
I have a database that a user enters a tracking number, example DEC009. The record is called by this tracking number. How can I sort records by month such as:

Jan001
Jan002
Mar011
Dec009

Thank you !

This conversation is currently closed to new comments.

3 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Access - sort by month

by Bob Sellman In reply to Access - sort by month

I assume you want to sort by correct month order, so a text sort will not work.

Build a query that will extract the first three characters of the tracking number and use that to create a date which you can then sort on.

To make the sort work, you may have to define two queries, the first to build an actual date column, the second to use that column for sorting. (Access doesn't seem to like to sort directly on the date column created in the first query.)

Assuming your tracking number column is called TrackNumber, the query (ignoring all other columns) would be defined with the following additional columns:

(This will extract the text for the month)
Month: Left$([TrackNumber],3)

(Next we create a date column using month. I've just defaulted to 2002 for the year, but if your tracking number also includes something for the date you could first create another column to extract the year and then include it to build the date in the expression below.)

TkDate: CDate([Month] & ", 2000")

Build this query and try running it. Assuming it works for you, try setting the query to sort on TkDate (or even TkDate and then TrackNumber). If it asks you to enter a value for month, drop the sorting and create a second query that takes all of the columns from this query that are in your data table, plus the TkDate column. You should then be able to define the sorting successfully for the second query.

Collapse -

Access - sort by month

by susanakil In reply to Access - sort by month

Thank you so much! It worked. S

Collapse -

Access - sort by month

by susanakil In reply to Access - sort by month

This question was closed by the author

Back to Software Forum
3 total posts (Page 1 of 1)  

Related Discussions

Related Forums