Access Hacks

Hack #44. Sort Any Arbitrary String of Characters

The Access query grid is great for sorting your data, but you need to help it sort on characters in the middle of a field.

I love the query grid. It's very helpful for doing all sorts of sorts (pun intended). But did you ever notice that sorting on text data always occurs on the whole field, going left to right? This makes sense because this is the most common sorting requirement. Imagine, though, the problem of having to sort on, say, just the fifth character, or the last three characters, or in any other way that isn't the norm.

This is an issue especially when Access is used with data that comes from other systems. Accounting systems are notorious for this. They often lump together a bunch of disparate data into a fixed-width field. Here's another classic problem: you are given a list of people's names in one field, structured as first name/last name, but you need to sort on just the last name.

Sorting in the Middle

Figure 5-12 shows a table filled with sales records. The records follow a strict format comprising a two-character vendor code and a six-character date; the remaining digits are the sales amount, and the last two of those digits are the decimal part of the amount. Therefore, the first SalesData record (CT1023044595) breaks down like this:

• Vendor code is CT.

• The date is October 23, 2004.

• The amount is $45.95.

Let's say you need to sort the records by date. As shown in Figure 5-12, in each record, the date starts in position ...