SQL (Query) sort the numeric values as string value

[Anna987]

Hello,

I have a problem with sorting on a numeric value, the sorting looks like a sort of a string (while field schuifid in table is numeric).
This is the sql :
SELECT schuifid, omschrijving FROM schuiven UNION SELECT"<Alle>"," " FROM schuiven ORDER BY schuifID;
The result = 1,10,100,101,11,110... in stead of 1,10,11,100,101,110.



Can someone help me with this?

Thanks in advance.

Anna

[June7]

What is <Alle>? Why is the field in UNION being treated as string? Format the number field so every value has the same number of characters padding with leading zeros.

ORDER BY Format(schuifid, "0000")

Use however many 0's needed for the length of largest possible number.

[Anna987]

The sorting is on a dropdown list and <Alle> is added as an item with union select to the dropdownlist, this field is not the field where the sorting is done.

[June7]

Okay, so are you saving schuifid into the bound field? Don't you want this column hidden so users don't see the number, just omschrijving values? Consider:

SELECT schuifid, omschrijving FROM schuiven
UNION SELECT Null, "<Alle>" FROM schuiven;

ColumnCount: 2
ColumnWidths: 0";1"

Note ORDER BY not needed.

[Minty]

To add something by way of an explanation - By adding 'Alle' to the initial Union query you are forcing access to implicitly convert your number data to text.
'Alle' can only be text , whereas 1,2,3,4 etc could be either. Access /SQL has to treat values in a way it can handle.

[CJ_London]

another way round this would be to include the values twice, one is numeric to be sorted on and once for display

SELECT schuifid, omschrijving AS N, omschrijving AS S FROM schuiven
UNION SELECT Null, 0, "<Alle>" FROM schuiven;

adjust column count, widths and bound column accordingly

sort on column N

[Anna987]

I now understand why this happens. The union select <All> is added to the numeric field and then i ORDER BY this field.
So the <Alle> (string) is in de number colomn, thats why the sorting is like string and not as numeric.
The reason why i add the <All> is to see all the records in a query when i select <All> in the dropdown list. I use this <all> value ias criteria in a query (IIf([forms]![leggerindeling].[OpzoekSchuifnummer]="<alle>";[schuifnummer];[forms]![leggerindeling].[OpzoekSchuifnummer])
How can I still use the <All> and also sort like a numeric field 1,2,3,4,5,100,101,...?

Thanks in advance.

[CJ_London]

see previous post (#6)

[Anna987]

I managed like Ajax said, make a new colomn with the same numeric value and sort this colomn.

Thanks to all for your support.

Anna

[June7]

Did you read post 4?