sort order w/ union query acting like text not num..

[kyosakc]

i'm using a union query in a combo box to add the "ALL" option to my selection list (as found around here as an faq i believe)but my problem is that the no matter what i seem to do it's treating my sort like texts not numbers
for ex after adding the union it sorts like this:

1
11
12390
12391
2
21
etc...

any way i can get it back into numerical order????

thnks

 

[scriverb]

You can use the CLng function on the column needing to be sorted in the query. This will convert it to a long integer and it should sort correctly.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[SkipVought]

Hi,

I have had to so something like this recently, where I concatenate a STRING and Number Padded with Zeroes in order to maintain a sort sequence.
[tt]
BOM.program & left("00000000",8-len( BOM.identifier))&BOM.identifier=[PAC].ProgID
[/tt]
where BOM.identifier is a LONG numeric type padded, as you can see to 8 digits.



Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[scriverb]

To give you an example of what I suggested here is some sample SQL:

SELECT A.*, A.[Sort_Field] 
FROM [i][red]yourtablename[/red][/i] as A 
ORDER BY CLng(A.[Sort_Field]);

This should do it.


[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]