Solved

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

Posted on 2014-09-23
5
210 Views
Last Modified: 2014-09-25
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?
0
Comment
Question by:Kevin Smith
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 40339612
just create a query with Order By

select * from tableName
Order by [nameof the column/field]
0
 
LVL 13

Expert Comment

by:Russell Fox
ID: 40339618
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
 

Expert Comment

by:karunamoorthy p
ID: 40339697
Attach access file or give sample records to proceed further
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40339760
I agree with Russell, something along the lines of:

ORDER BY Val(Mid([yourColumn], 2))
0
 

Author Closing Comment

by:Kevin Smith
ID: 40344164
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

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question