How to sort an Access column with numbers that begin with a letter...?

I have a column in Access where all the records begin with the letter O followed by numbers. How do I sort this column and keep the numbers in order?
Kevin SmithAsked:
Who is Participating?
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
just create a query with Order By

select * from tableName
Order by [nameof the column/field]
0
 
Russell FoxDatabase DeveloperCommented:
You might need to chop off the "O" and cast the numbers into an INT. Are the numbers of varying lengths, like:
O123
O1234
Or all the same, like
O123456
O123457
O123458

If they're all the same length, then you should just be able to sort the column as-is, but if they vary you'll need to remove the O:
CInt(Right([YourColumn], LEN([YourColumn])-1)
0
 
karunamoorthy psenior system analystCommented:
Attach access file or give sample records to proceed further
0
 
Dale FyeCommented:
I agree with Russell, something along the lines of:

ORDER BY Val(Mid([yourColumn], 2))
0
 
Kevin SmithAuthor Commented:
At first I thought: "duh, I know how to sort...gimme some credit." Ha. But my syntax and order was a little wonky so it wasn't sorting. I assumed it wasn't sorting 'cause of the O...turns out it was as simple as that.  Thanks to everyone!
0
All Courses

From novice to tech pro — start learning today.