SQL Syntax

Posted on 2012-03-28
Last Modified: 2012-06-21
I am trying to order my recordset by the last 1 or 2 numbers from a value that always starts with a letter.  For example the value is B10, I want to order it by 10.

So what is the syntax for this in ORDER BY?

Here is the sql.  The ORDER BY row has psuedo code.

 strSql = "SELECT ServCat, Number, Division, Coded, Reason, ServCatDesc " _
    & "FROM tblServCats " _
    & "WHERE Div = '" & vDiv & "' " _
    & "ORDER BY  mid(ServCat , 1, 2)"

Question by:ScootterP
  • 2
  • 2
LVL 61

Expert Comment

ID: 37777972
Pad it with zeros to make sure it orders numerically - and I think you might need a corresponding SELECT column for the Order BY:

strSql = "SELECT ServCat, Format(mid(ServCat , 1, 2),'000000') AS SortingColumn, Number, Division, Coded, Reason, ServCatDesc " _
    & "FROM tblServCats " _
    & "WHERE Div = '" & vDiv & "' " _
    & "ORDER BY  Format(mid(ServCat , 1, 2),'000000')"
LVL 75

Accepted Solution

DatabaseMX (Joe Anderson - Access MVP) earned 500 total points
ID: 37777979
Try this

& "ORDER BY  CLng(mid([ServCat] , 2))"
LVL 61

Expert Comment

ID: 37777994
Sorry - try this:

strSql = "SELECT ServCat, Format(mid(ServCat , 2),'000000') AS SortingColumn, Number, Division, Coded, Reason, ServCatDesc " _
    & "FROM tblServCats " _
    & "WHERE Div = '" & vDiv & "' " _
    & "ORDER BY  Format(mid(ServCat , 2),'000000')"

Author Closing Comment

ID: 37778000
Beautiful.  Thanks.
LVL 75
ID: 37778024
You are welcome ...

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SPROC to look for existing record in passed table name 7 49
MS Access 2016 Debugging 7 43
Running Access application from Task Scheduler 6 34
Is it possible to reset DSum? 12 43
This article is a continuation or rather an extension from Cascading Combos ( and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

896 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now