Problem to sort a field

[azlina]

hola....
i got a problem..actually i want to sort a field in database..but the
field has char and num value..i need to sort it ascending by
number...like this A1,A2,A3,A100,A200,A300..bcoz when i have sort it
asc it become like this..A1,A100,A2,A200,A3,A300...can u help me with
this problem??plz..

 

[pietlinden]

separate the text part of the value and the numeric part and then sort
by the text field then the numeric field.

 

[John Vinson]

hola....
i got a problem..actually i want to sort a field in database..but the
field has char and num value..i need to sort it ascending by
number...like this A1,A2,A3,A100,A200,A300..bcoz when i have sort it
asc it become like this..A1,A100,A2,A200,A3,A300...can u help me with
this problem??plz..

Ideally, do as Pietlinden says and separate the field.

If the text portion is always one byte long, create a calculated field

Val(Mid([fieldname], 2))

and sort by it.

John W. Vinson[MVP]

 

[azlina]

tq john and pietlinden..
but...i want the fie name still have A infront of the numbers..actualy
my real file name are..A10.bmp,A103.bmp,A4.bmp,A401.bmp...can i sort it
to be like this A4.bmp,A10.bmp,A103.bmp,a401.bmp???

hola....
i got a problem..actually i want to sort a field in database..but the
field has char and num value..i need to sort it ascending by
number...like this A1,A2,A3,A100,A200,A300..bcoz when i have sort it
asc it become like this..A1,A100,A2,A200,A3,A300...can u help me with
this problem??plz..

Ideally, do as Pietlinden says and separate the field.

If the text portion is always one byte long, create a calculated field

Val(Mid([fieldname], 2))

and sort by it.

John W. Vinson[MVP]

 

[John Vinson]

tq john and pietlinden..
but...i want the fie name still have A infront of the numbers..actualy
my real file name are..A10.bmp,A103.bmp,A4.bmp,A401.bmp...can i sort it
to be like this A4.bmp,A10.bmp,A103.bmp,a401.bmp???

So? You can *sort* by the calculated field, and *display* the
filename. Did you try it?

John W. Vinson[MVP]

 

[azlina]

yeah...i've try that...but i got a problem when run it..it said
"undefine function in expression"

 

[pietlinden]

how about posting the SQL? My psychic powers aren't working right
now...

 

[John Vinson]

yeah...i've try that...but i got a problem when run it..it said
"undefine function in expression"

Then fix the problem in the query.

If you would like help doing so, please open the query in SQL view and
post the SQL text here. We can't see it from this distance!

John W. Vinson[MVP]