By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,121 Members | 1,782 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,121 IT Pros & Developers. It's quick & easy.

MS Access Skip the first character in a field and then sort

P: 105
Hey everyone!

I'm working in Access 2003 and need help with the 'order by' in a query. I have an CrewCode that includes a letter as well as numbers, and I want to sort by the numbers. The CrewCodes are for instance:


When I just sort by ascending, it's all out of order. How would I do this?

Dec 19 '08 #1
Share this Question
Share on Google+
3 Replies

P: 105
Hey yall,

I figured out a solution!
Expand|Select|Wrap|Line Numbers
  1. SELECT IIf(IsNull([CrewCode]),"",Format$(Right([CrewCode],Len([CrewCode])-1),"0000")) AS [Check]
  2. FROM tblCrews
  3. ORDER BY IIf(IsNull([CrewCode]),"",Format$(Right([CrewCode],Len([CrewCode])-1),"0000")) DESC;
Dec 19 '08 #2

Expert 5K+
P: 8,638
A slightly different approach:
Expand|Select|Wrap|Line Numbers
  1. SELECT IIf(IsNull([CrewCode]),"",Format$(Mid$([CrewCode],2),"0000")) AS [Check]
  2. FROM tblCrews
  3. ORDER BY IIf(IsNull([CrewCode]),"",Format$(Mid$([CrewCode],2),"0000")) DESC;
Dec 20 '08 #3

Expert Mod 15k+
P: 31,494
That's a neat solution :)

Please though, remember in future to use the [ CODE ] tags provided. As a full member now, I wouldn't expect to be needing to say this.
Dec 22 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.