MS Access Skip the first character in a field and then sort | Member | | Join Date: Oct 2007
Posts: 102
| | |
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:
D5159
D1801
D337
D5276
When I just sort by ascending, it's all out of order. How would I do this?
THANKS SO MUCH!!!
| | Member | | Join Date: Oct 2007
Posts: 102
| | | re: MS Access Skip the first character in a field and then sort
Hey yall,
I figured out a solution! - SELECT IIf(IsNull([CrewCode]),"",Format$(Right([CrewCode],Len([CrewCode])-1),"0000")) AS [Check]
-
FROM tblCrews
-
ORDER BY IIf(IsNull([CrewCode]),"",Format$(Right([CrewCode],Len([CrewCode])-1),"0000")) DESC;
|  | Expert | | Join Date: Apr 2006 Location: Philadelphia
Posts: 5,216
| | | re: MS Access Skip the first character in a field and then sort
Quote:
Originally Posted by jmarcrum  Hey yall,
I figured out a solution!
SELECT IIf(IsNull([CrewCode]),"",Format$(Right([CrewCode],Len([CrewCode])-1),"0000")) AS [Check]
FROM tblCrews
ORDER BY IIf(IsNull([CrewCode]),"",Format$(Right([CrewCode],Len([CrewCode])-1),"0000")) DESC; A slightly different approach: - SELECT IIf(IsNull([CrewCode]),"",Format$(Mid$([CrewCode],2),"0000")) AS [Check]
-
FROM tblCrews
-
ORDER BY IIf(IsNull([CrewCode]),"",Format$(Mid$([CrewCode],2),"0000")) DESC;
|  | Administrator | | Join Date: Oct 2006 Location: London - UK
Posts: 15,730
| | | re: MS Access Skip the first character in a field and then sort
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.
|  |
| | |  /bytes/about
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 226,449 network members.
|
