How to stop auto sort on primary key tables in ms access

[Dibe]

I would like to ask about primary keys in access. My program creates its keys from the last key in the database. It read the current last key in DB then calculations to come with new key. Problem is when it reaches 100 access sort it and put this 100 under 10. The sequence is ...09,10,11,12. ..99 then sorted to 09,10,100,11... 99. Example key AAA000. Please help

[Rabbit]

Then you need to change your sequence to 3 significant digits. i.e., 001, 002, 003, etc.

[jimatqsi]

Your complaint is about how 100 sorts in the list, but have you thought 1000 and 10000?

This is a problem anytime you want a numeric to be part of a string. You have to decide up front, before you put down the very first key, how many digits, at maximum, can the numeric part be. Then you have to provide the leading zeroes, so one looks like 000001 if 6 is your chosen length for the numeric part of the string.

One way to turn a numeric into a string with leading zeroes is like this:

dim intN as integer
dim strA as string
int n = 10
strA = "ABC"
strA = strA & right(intN + 1000000,6)

the result will be strA="ABC000010 "
Jim

[zmbd]

Dibe
Here is why you are seeing this: Sort records on text, numeric, or date values
Both Rabbit and jimatqsi have very good ideas.
What I would suggest you do is:
Create a new primary key, use auto-number. Do not use this for anything except as a unique means to identify the record and for linking to other tables.
Separate the alpha-numeric portions of the "key" into their own fields and use a calculated field within a query, form, report to combine them if required. You can now sort on the fields as required. You don't have to worry about text length, or numeric length until you need formatted output.
MORE IMPORTAINTLY: If for some reason the "key" format, or information must be changed, it doesn't messup your relationships in other tables. I work in a lab and we use lot numbers for samples and production; however, over the years these lot number formats have changed. The primary key in my database didn't because it was an auto-number and therefore none of the other records needed to be altered (for 1000's of entries across dozens of tables!)

(Now, there is a means where in you could create a compound key, even a primary key, using the two fields that I suggest you use for your “key.” There are certainly reasons to do this and I do so upon occasions when I need to assure unique combinations; however, I rarely use these as the primary key as setting up relationships between tables using a compound primary key and VBA programing can get problematic).