Sorting ignoring first letter of data?

[lawmans3]

I have a list of badge numbers and I would like to sort them numerically but since they have a letter in front access doesn't sort them the way I would like. Here is an example of the data:



P1
P215
P75
P5

I've tried using the MID function like =MID([Badge#],1) but I can't get it to work. Any help would be appreciated!

Thanks,
mitch

[June7]

Try:

Mid([Badge#],2)

or

Val(Mid([Badge#],2))

BTW, advise no spaces or special characters/punctuation (underscore is exception) in names. Better would be BadgeNum or Badge_Num.

[Rawb]

If the Badge numbers can vary in length (some are 3 characters, some 4, etc.) then you may also need to cast the resulting value to a number. If not, it will probably sort the result as strings (so 749 will show up before 75):

CLng(Mid([Badge#],2))

This will force Access to treat the numbers as Longs (Integers that support very large values) and may give you a more expected result when sorting.