A - Z

[skollager]

How can i stop access doing the following on a report or form.
A1
A10
A11
A2
etc

I would prefer it to do the following.

A1
A2
A3
etc
A10
A11
A12
etc
B1
B2
B3
etc

 

[charityg]

Boy have I had this same problem a zillion times!! The thing is, Access is sorting on a text field (A before B, 1 before 2). You need to give it 2 sorting specifications. The first is left(fieldname,1) which will trim the letter off of the field to sort by, and then right(fieldname, len(fieldname)-1) will give you just the numeric portion of your field. Sort by the letter first and then the number and that should work.

 

[Rich@ITTC]

Hi Skollager

I agree with Charityg but have a minor suggestion. This field is a text field (as it accepts both letters of the alphabet and numbers) and the sort order for such a datatype will follow the value of each character in turn - A, A, A, A, B, B, B, C, C, etc taking precedence (rightly so) but then all the 1s in the second place (hence 1,10,11,12,13,14,15,16,17,18,19,2,21,22, etc).

To get round this you create a query and add two calculated fields to sort the first character/letter A,B,C and then make another calculated field/expression to sort the number ... but the number will still follow 1,11,12 ... 17,18,19,2,21, etc.

So the second expression needs to change the text characters 1,2,3,etc back to numbers.

1st expression:

SortAlpha: (Left([YourField],1))

2nd expression:

SortNum: CInt(Right([YourField],Len([Yourfield])-1))

Therefore, in the query, in design view, you have reading from the left - YourField, SortAlpha, SortNum. Both SortAlpha and SortNum are then sorted in ascending order, but neither need to ticked in Show.

The SQL is as follows:

SELECT tblYourTable.YourField
FROM tblYourTable
ORDER BY (Left([YourField],1)), CInt(Right([YourField],Len([YourField])-1));

HTH

Rich Gorvin

[This message has been edited by Rich@ITTC (edited 04-21-2001).]