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

[jmarcrum]

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!!!

[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;

[ADezii]

@jmarcrum
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;

[NeoPa]

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.