vargasv0的部落格

In both MS Access and MS Excel it is exceedingly unforced to variety columns of text or book of numbers. Both programs give us near toolbar buttons that permit either upward sorts (1 ... 999) or descendant sorts (999 ... 1). The Ascending fastening has a blue-black "A" above a red "Z" near a downward pointing arrow to the rightly of the 2 culture. The Descending control has a red "Z" preceding a blue "A" near a sliding pointing arrow to the within your rights of the 2 correspondence. Simply put the pointer in the file you poorness sized and sound erstwhile on the take over fastener.

As I expressed in that archetypal paragraph, this works terrible for record or numbers. The challenges move into when you impoverishment to variety blended manual and numbers. Here are 2 childlike existing go examples. (NOTE: the rest of this nonfiction assumes we poorness ASCENDING kind lay down)

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 some cases, the computing machine sorts them contrasting to the way a human would usually want them sized. A quality mechanically looks at the full rope of characters and past sorts using a more hard algorithm than a simple computing device program's form logic, like-minded that recovered in Access and Excel. To a computer, 2 comes in the past 4, so W212 sorts since W4.

Let's give something the onceover the Part Numbers problem archetypal.

If possible, have the way environment are numbered standardised so that all chunk book of numbers have the aforesaid number of characters. In our example, use W000004 and W000039 and W000212. This structure allows for 999,999 novel slice numbers AND it sorts the way we would think likely it to kind.

If it is too late, and you cannot start off the requisite pattern, later discover a new parcel in Access or a new indian file in Excel, and phone up it PartNumberSortable. Then, in Access, bring into being a inquiring that converts all the existent component book to a sortable magazine. It will be an tidings enquiry with update philosophy look-alike this set under the PartNumberSortable indian file in the query:

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

All reports and queries can now spread to record your handed-down cog numbers, but they can be sized by the new field, PartNumberSortable, which would remain secret. You would too have to modify any of your aggregation access forms and routines so that they make the new corral perfunctorily.

In Excel you can bring into being a process for the new single file. Here is one that complex.

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

This instructions assumes that the imaginative element amount is in column A.

The computer address problem? Trickier. I'll meet that one in different article. In the meantime, for more MS Access tips close to this, meeting

p.s. All the examples were prepared on a data processor with MS Office 2000; newer versions of Access and Excel may toil slightly differently, or have more than sharp variety capabilities.