Creating a custom sort in Microsoft Access

It is easy enough to do a custom sort in Microsoft Excel, but what about in Access. In Access, you can create a calculated field using IIf in order to be able to do so.

As an example, if you have a field called MONTH, you may want it sorted Jan, Feb, Mar, etc, rather than starting with April.

Open the table in Datasheet view, then on the Home tab, in the Sort & Filter group, click Advanced, then from the shortcut menu, click Advanced Filter/Sort.

Add any fields to be included in your query to the grid.

In the first blank column, click in the Field row, and type in:

IIf([Month]=”January”,1,IIf([Month]=”February”,2, IIf([Month]=”March”,3, IIf([Month]=”April”,4, IIf([Month]=”May”,5, IIf([Month]=”June”,6, IIf([Month]=”July”,7, IIf([Month]=”August”,8, IIf([Month]=”September”,9, IIf([Month]=”October”,10, IIf([Month]=”November”,11, IIf([Month]=”December”,12))))))))))))

Month is the name of the field containing the values to be sorted.

In the Sort row for the column containing the expression, click Ascending.

On the Home tab, in the Sort & Filter group, click Toggle Filter.

Your field will be sorted in the required order.

Find out more about our Microsoft Access training courses Sydney.

Find out more about our Microsoft Access training courses London, UK or our Microsoft Access training courses Dorset, Hampshire, Wiltshire, UK

About jdonbavand

I am a trainer of Microsoft Office, Microsoft Project and Crystal Reports. I have called my blog "If Only I'd Known That...." because I hear it so many times in training sessions. In fact, if only I had a £100 (or 150 Aussie dollars)for every time someone says "If only I'd known that." ....
This entry was posted in Microsoft Access, Microsoft Access 2007, Microsoft Access 2010 and tagged , . Bookmark the permalink.

4 Responses to Creating a custom sort in Microsoft Access

  1. Sjoerd says:

    This only works with a small amount of data to be sorted, but not with a large amount.
    For example if I have a list of over 30 ranks to sort in the order I want, how to do that in access/

  2. Scott M says:

    I had the same issue. I’m tracking sizes of my t-shirt orders and I like them to be in the order XS, S, M, L, XL, 2XL, etc. Clearly not alphabetical–especially with Youth sizes in front of them! I solved this by creating a new table (tb_SortOrder) with all possible size entries; and I assigned each a “rank”. I used 110, 120, 130, etc to leave some room between them for future additions. I linked the table sorted by “rank”.

  3. Asanka says:

    Im having an issue. I tried to do the same. but when I click toggle filter it is asking a parameter for all 12 months. what can I do. pls help.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s