How to Use Advanced Filter/Sort in Access 2007

The Advanced Filter/Sort function in Access 2007 allows you to sort data in a user-defined order rather than the sort orders defined for that data type. For example, if you have a field containing the weekdays, you would probably prefer to sort it in order of the days of the week rather than alphabetical order. Here's how to use an advanced filter or sort in Access 2007.

Instructions

1. Open the table you wish to sort in a user-defined order using the Datasheet view.

2. Click on the "Home" tab and look in the "Sort&Filter" group for the "Advanced" icon. Click on this icon to bring up the shortcut menu and click on "Advanced Filter/Sort."

3. Add the fields you wish to sort on to the grid. Move the cursor over the "Field" row in a column for which you wish to define a sort order. Press the right mouse button to bring up the context menu and select "Zoom."

4. Enter an expression which defines your desired sort order. In this example, we want to sort the days of the week by their order in the week so that Monday has the highest precedence and Sunday has the lowest precedence. Assume that the name of the field is this example is Weekday.

5. Write an expression that looks like this: IIf([Weekday] = "Monday" 1, IIf([Weekday] = "Tuesday" 2, IIf([Weekday] = "Wednesday" 3, IIf([Weekday] = "Thursday" 4, IIf([Weekday] = "Friday" 5, IIf([Weekday] = "Saturday" 6, IIf([Weekday] = "Sunday" 7, 8))))))).

Resources

• Learn more about advanced sorting in Access 2007.