Report sorting

[Tom Tee]

I have a table which has a column of text with numbers. ie; S976S, or S934L.
The leading letter never changes. I have now had to go to four numerical
digits but it does not sort right. If I add a leading zero, how would I do
the sort?

 

[John Spencer]

one method would be to use calculated fields for the sort

Left(YourField,1)
Val(Mid(YourField,2))
YourField

Another would be to build your sort field as follows
Assumption: your field is one letter followed by a three or four digit
number followed by one letter

Left(YourField,1) & Format(Val(Mid(YourField,2)),"0000") &
Right(YourField,1)

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..