Sort text field correctly 2

[danwand]

Hi,

I have a table which is to be sorted by the field ProcedureReferral, this field is a text field but contains the following example values (list 1):

A1
A2
A10
A11
B2
B3
B4
B9
B10

I want to sort a query by these values however when i set it up the query sorted as follows (list 2):

A1
A10
A11
A2
B10
B2
B3
B4
B9

Does anyone have any suggestions how to make the query sort the same as the list 1 rather than list 2?

Any help would be appreciated
Thanks Dan

 

[SJMcAbney]

Use a text function in a query to split the two aprt and sort on them individually.

i.e.


SELECT Left([MyField], 1) As TextPart, Mid([MyField], 2) AS NumberPart
FROM MyTable
ORDER BY Left([MyField], 1), Mid([MyField], 2);

Stewart J. McAbney | Talk History

 

[PHV]

ORDER BY Left(ProcedureReferral,1), Val(Mid(ProcedureReferral,2))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[danwand]

Excellent work guys.

Thanks very much

Dan