Sort Table 2

[markedback]

I have a table that needs to be sorted by the ID number. Some of the ID numbers are 5-digit and some are 9-digit. When I try to sort the table currently, all the ID numbers sort in the order of the number in the first digit position irrespective of the number of digits.

I would like to sort the table by the ID number with the 5-digit numbers clubbed together and the 9-digit numbers clubbed together.

Please help.

Thanks,
Mark.

 

[dhookom]

If you would have typed in 4 to 5 numbers, it would have been very easy to visualize what you have and what you want. I can guess that you want to sort by numeric value in a form or report or query. You can generally do this by wrapping your field name in the Val() function:
=Val([ID])

If this doesn't meet your specs, come back with examples and a more technical description of "clubbed together".

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

ORDER BY Len(ID), ID

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[markedback]

Sorry for the "un-technical" language, dhookom. I was writing the question in a hurry. I will try to be more technical in the phrasing next time. I just wanted the right syntax for the 'order by' statement.

Thanks PHV, your solution worked!