ORDER BY in Query won't sort properly 2

[rccline]

I have a select query that creates an Alias named Volume. Volume contains text and numbers. I want to sort the field Volume first by text (letters, a, b, c, d etc.) and then by numbers (1, 2, 3, etc.).

I can get the letters first then numbers in the query results. The numbers are sorting correctly, but the letters are sorting by c, b, x. Why would c come before b?



SELECT tblInstruments.InstrumentID, tblInstruments.Vol_txt,
tblInstruments.Vol_Num, IIf(IsNull([Vol_txt]),[Vol_num],[Vol_txt]) AS
Volume, tblInstruments.Page
FROM tblInstruments
ORDER BY IIf(IsNull([Vol_txt]),[Vol_num],[Vol_txt]);


You an change the sort order to start with text, then alphabet using Val
in the Order statement:

SELECT tblInstruments.InstrumentID, tblInstruments.Vol_txt,
tblInstruments.Vol_Num, IIf(IsNull([Vol_txt]),[Vol_num],[Vol_txt]) AS
Volume, tblInstruments.Page
FROM tblInstruments
ORDER BY Val (IIf(IsNull([Vol_txt]),[Vol_num],[Vol_txt]));



Thank you.

Robert

 

[PHV]

Perhaps this ?
SELECT InstrumentID, Vol_txt, Vol_Num, Nz([Vol_txt],[Vol_num]) AS Volume, [Page]
FROM tblInstruments
ORDER BY IIf(IsNull([Vol_txt]),1,0), Vol_txt, Vol_Num

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

 

[Golom]

VAL(ANY ALPHA CHARACTER) = 0 so everything that starts with a letter is essentailly unsorted because they have the same ORDER BY values.

If you want both a numeric and an Alpha sort then

ORDER BY Val(IIf(IsNull([Vol_txt]),[Vol_num],[Vol_txt])), [Vol_txt]

 

[rccline]

Yes! That worked. Thanks. Can you explain why?
I see if Vol_txt is null, then 1 otherwise 0.
then sort Vol_txt, vol_num.

Looks like I'm not understanding the 1 and 0. Maybe more??

Thanks.

Robert

 

[PHV]

0 comes before 1, so you get vol_txt before vol_num

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