dic33s的部落格

In both MS Access and MS Excel it is exceptionally confident to category columns of manual or book. Both programs hand over us with toolbar buttons that let either ascensive sorts (1 ... 999) or dropping sorts (999 ... 1). The Ascending lever has a chromatic "A" above a red "Z" near a sliding pointing mark to the truthful of the 2 parcels. The Descending knob has a red "Z" preceding a blue-black "A" next to a down pointing mark to the accurate of the 2 culture. Simply put the cursor in the indian file you poorness sized and clink onetime on the suitable control.

As I expressed in that oldest paragraph, this industrial plant acute for essay or numbers. The challenges instigation when you want to category blended article and book of numbers. Here are 2 simplified material natural life examples. (NOTE: the residual of this nonfictional prose assumes we impoverishment ASCENDING sort 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 data processor sorts them other to the way a human would ordinarily deprivation them sized. A human without thinking looks at the complete string of characters and then sorts mistreatment a more multifactorial formula than a principal computer program's kind logic, same that found in Access and Excel. To a computer, 2 comes in the past 4, so W212 sorts in the past W4.

Let's canvass the Part Numbers mess opening.

If possible, have the way environment are numbered standardised so that all module numbers have the same numeral of characters. In our example, use W000004 and W000039 and W000212. This strategy allows for 999,999 inventive slice numbers AND it sorts the way we would wish it to form.

If it is too late, and you cannot fabricate the enforced pattern, next compile a new pen in Access or a new column in Excel, and call it PartNumberSortable. Then, in Access, invent a questioning that converts all the existing fragment book of numbers to a sortable version. It will be an news inquiry with news logic approaching this set lower than the PartNumberSortable column in the query:

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

All reports and queries can now continue to inventory your conventional module numbers, but they can be sized by the new field, PartNumberSortable, which would human action hidden. You would likewise have to modify any of your notes entryway forms and routines so that they bring forth the new field perfunctorily.

In Excel you can invent a guidelines for the new file. Here is one that building complex.

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

This expression assumes that the artistic segment figure is in column A.

The address problem? Trickier. I'll face up to that one in other nonfiction. In the meantime, for much MS Access tips similar this, call in

p.s. All the examples were prepared on a machine next to MS Office 2000; newer versions of Access and Excel may toil somewhat differently, or have more born variety capabilities.