List box sort order 1

[BrianLe]

I have a list box that gets its values from tblTrainNoQuery. The TrainNo "values" are actually text in the format 2007PAT1, 2007PAT2, ... up to say 2007PAT100. When the TrainNo's are sorted in ascending order, they are 2007PAT1, 2007PAT10,2007PAT11, ... 2007PAT2, 2007PAT20, etc. How do I sort them so they are in numerical order, i.e. 2007PAT1, 2007PAT2,... 2007PAT9, 2007PAT10,etc.?

Thanks, Brian

 

[PHV]

Modify the SQL code like this:
... ORDER BY Val(Mid(TrainNo,8)), TrainNo

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[BrianLe]

Works great. Thanks