ORDER BY question

[cdolphin88]

Hello,

I have a table with a field called nbr in text format.

When I wrote the query

SELECT nbr
FROM table1
ORDER BY nbr ASC

it shows me like this


nbr
1
10
2

But if I change the format of nbr to number it will shows me

nbr
1
2
10

My question is can I somehow write a query which show me the second
ORDER (like 1,2,10 ) with field nbr as format text?? BEcause I need to
add 0 in the left of the nbr (like this: 001, 010, 002) and if I put in
number format, the 0 won't show..

Can someone has any idea?

Cheers!

C

 

[Guest]

In your query add a field to sort on like this ---
My_Sort: Val([nbr])

 

[Guest]

try the following:

Set the fiel as a number field in data type
In field options set the format as 000

this forces a three character number display including lead zeros

The numbers will sort numerically rather than textually, with the leading
zeros, which I think is what you want

OK

Jim Normile