Query to sort number sequential

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am stuck with a DB that has address like 1st, 10st, 2st, and 20st and so
on. Current query sorts as seen above. I would like it to sort sequentialy
like 1st, 2st, 10st, and 20st. Is it posible and if so how would it be done.

Thanks.
 
Nick said:
I am stuck with a DB that has address like 1st, 10st, 2st, and 20st and so
on. Current query sorts as seen above. I would like it to sort sequentialy
like 1st, 2st, 10st, and 20st.
Click to expand...

Something like

ORDER BY RIGHT$('000' & data_col, 6)

Jamie.

--
 
If the field is just the data you have shown, and if the last two characters
are always extra, you could base a query on the table, then do something
like this in an empty column in the query design grid:

NewField: Left([Address],Len([Address]) - 2)

Then sort by that field. This assumes that the troublesome field is named
[Address].
 
I think this is the right way to go but I am unable to get it to work. If you
would help me further. The field may has more than 10 number or it will have
street names such as Beartrail and so on. If you could define it more. The
field name for this data is [AddressID]
 
You will need to provide some sample data. There have been three separate
suggestions so far, and you are still stuck, so more information is needed.

Nick said:
I think this is the right way to go but I am unable to get it to work. If
you
would help me further. The field may has more than 10 number or it will
have
street names such as Beartrail and so on. If you could define it more. The
field name for this data is [AddressID]

Jamie Collins said:
Something like

ORDER BY RIGHT$('000' & data_col, 6)

Jamie.
Click to expand...
Click to expand...
 
Back
Top