alphanumeric sorting

  • Thread starter Thread starter Michael Bentfeld
  • Start date Start date
M

Michael Bentfeld

Hello,

I have an access database that tracks returns of faulty
parts. Each return has a unique return number, originally
a "P" followed by a 3-digit number. Recently, the return
numbers passed the 1000 mark. The database currently uses
alphanumeric sorting, so that when a user clicks the "Last
Record" button to see the highest return number, the
numbers are still showing in the P997 (P998 and P999 were
skipped), when the latest number is P1010. Is there a way
to change the way the database sorts the records so that
when someone clicks on the "Last Record" button, the
record for return number P1010 shows up? Please help!

Thanks,

Michael
 
Try this in your query, substituting the correct field and tablenames for
YourField and tblWhatever:

SELECT YourField, Left([YourField],1) & Format(Mid([YourField],2),"0000") AS
SortField
FROM tblWhatever
ORDER BY Left([YourField],1) & Format(Mid([YourField],2),"0000") DESC;

--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
 
Back
Top