seuuei的部落格

In some MS Access and MS Excel it is massively soft to variety columns of workbook or book of numbers. Both programs secure us with toolbar buttons that allow either ascending sorts (1 ... 999) or descendant sorts (999 ... 1). The Ascending toggle has a blue "A" above a red "Z" near a sliding pointing arrow to the exact of the 2 junk mail. The Descending toggle has a red "Z" preceding a pitch-black "A" beside a downhill pointing pointer to the correct of the 2 packages. Simply put the indicator in the file you poorness sized and sound former on the decorous control.

As I declared in that first-year paragraph, this plant extreme for paper or book. The challenges activate when you poorness to kind blended deed and book. Here are 2 plain genuine existence examples. (NOTE: the scrap of this piece assumes we poorness ASCENDING category directive)

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

• W4
• W39
• W212

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

• 4 Main Street
• 39 Main Street
• 212 Main Street

In both cases, the electronic computer sorts them other to the way a quality would in general poverty them sized. A human mechanically looks at the entire yarn of characters and consequently sorts victimisation a more than labyrinthine algorithmic rule than a rough information processing system program's sort logic, suchlike that saved in Access and Excel. To a computer, 2 comes before 4, so W212 sorts before W4.

Let's measure the Part Numbers difficulty original.

If possible, have the way surround are numbered standardized so that all division numbers have the same digit of characters. In our example, use W000004 and W000039 and W000212. This hatch up allows for 999,999 characteristic part of the pack numbers AND it sorts the way we would predict it to sort.

If it is too late, and you cannot turn out the requisite pattern, after compile a new corral in Access or a new column in Excel, and beckon it PartNumberSortable. Then, in Access, start off a question that converts all the surviving quantity numbers to a sortable interpretation. It will be an news enquiry near news philosophy approaching this settled below the PartNumberSortable single file in the query:

left([PartNumber],1) & information(mid([PartNumber],2),"000000")

All reports and queries can now keep on to record your old-world chunk numbers, but they can be sized by the new field, PartNumberSortable, which would pass the time underhand. You would too have to make to order any of your collection vestibule forms and routines so that they create the new enclosed space involuntarily.

In Excel you can craft a method for the new single file. Here is one that building complex.

=LEFT(A1,1) & RIGHT("000000" & MID(A4,2,8),6)

This procedure assumes that the inspired section amount is in file A.

The address problem? Trickier. I'll face that one in another article. In the meantime, for more than MS Access tips similar to this, visit

p.s. All the examples were processed on a data processor with MS Office 2000; newer versions of Access and Excel may industry a little bit differently, or have more than agile kind capabilities.