Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2014-09-23
5
Medium Priority
?
214 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 2000 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 48

Expert Comment

by:Dale Fye
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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

715 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