Ms Access And Ms Excel - What To Do When Sorting Does Not Work

Ms Access And Ms Excel - What To Do When Sorting Does Not Work

Summary: In both Microsoft Access and Microsoft Excel it is very easy to sort columns of text or numbers. Both programs supply us with toolbar buttons that allow either ascending sorts ( ... ) or descending sorts ( ... ). The Ascending button has a blue "A" above a red "Z" with a downward pointing arrow to the right of the letters. The Descending button has a red "Z" above a blue "A" with a downward pointing arrow to the right of the letters. Simply put the cursor in the c...

ms access,ms excel,sort,sorting,microsoft access

Article Body: In both Microsoft Access and Microsoft Excel it is very easy to sort columns of text or numbers. Both programs supply us with toolbar buttons that allow either ascending sorts ( ... ) or descending sorts ( ... ). The Ascending button has a blue "A" above a red "Z" with a downward pointing arrow to the right of the letters. The Descending button has a red "Z" above a blue "A" with a downward pointing arrow to the right of the letters. Simply put the cursor in the column you want sorted and click once on the appropriate button.

As I stated in that first paragraph, this works great for text or numbers. The challenges start when you want to sort mixed text and numbers. Here are simple real life examples. (NOTE: the remainder of this article assumes we want ASCENDING sort order)

Example : Part Numbers. On a computer, which comes first?

- W - W - W

Example : Addresses. On a computer, which comes first?

- Main Street - Main Street - Main Street

In both cases, the computer sorts them opposite to the way a human would normally want them sorted. A human automatically looks at the entire string of characters and then sorts using a more complex algorithm than a basic computer programs sort logic, like that found in Access and Excel. To a computer, comes before , so W sorts before W.

Lets examine the Part Numbers problem first.

If possible, have the way parts are numbered standardized so that all part numbers have the same number of characters. In our example, use W and W and W. This scheme allows for , unique part numbers AND it sorts the way we would expect it to sort.

If it is too late, and you cannot create the required pattern, then create a new field in Access or a new column in Excel, and call it PartNumberSortable. Then, in Access, create a query that converts all the existing part numbers to a sortable version. It will be an update query with update logic like this placed under the PartNumberSortable column in the query:

left([PartNumber],) & format(mid([PartNumber],),"")

All reports and queries can now continue to list your traditional part numbers, but they can be sorted by the new field, PartNumberSortable, which would stay hidden. You would also have to modify any of your data entry forms and routines so that they generate the new field automatically.

In Excel you can create a formula for the new column. Here is one that works.

=LEFT(A,) & RIGHT("" & MID(A,,),)

This formula assumes that the original part number is in column A.

The address problem? Trickier. Ill tackle that one in another article. In the meantime, for more MS Access tips like this, visit http://www.databaselessons.com

p.s. All the examples were processed on a computer with MS Office ; newer versions of Access and Excel may work slightly differently, or have more intelligent sort capabilities.