Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Sort Table 2

Status
Not open for further replies.
Feb 25, 2008
46
US

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.
 
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
 
ORDER BY Len(ID), ID

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

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!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top