Generate Unique table ID

[rwahdan1978]

Hi



I have a table that has invoiceID as the primary key but it is not auto number, it is string. what I want to do is in the form when it is loaded, I want to following this format:

"Py-000" then add a number and keeps incrementing.

Example: "Py-0001","Py-0002","Py-0003"...

I know that I need to save the counter in the table but how to do that when mixing string and number?

[Micron]

IMO you should have an autonumber field for unique record id and not use real data as your pk. Then 1 field for prefix because that may get augmented at some point. Then a field for the incremental number. In forms, queries or reports you work with the id when necessary, and concatenate the prefix and number where required (in form/report controls).

[ano]

correct answer but if sequence # has to be steady ( like last record deleted , new record # has gap) opention is look up the max(#) remove the prefix add 1 and ok na

[June7]

Generating a custom unique identifier is a common topic. Review https://www.accessforums.net/showthread.php?t=23329

If it will always be "PY", why bother saving this in field? It can be added in expression with concatenation or textbox formatting when needed.

[Micron]

why bother saving this in field

Because you can never say never? What if the boss says, "we are starting a new prefix - add it to the database". Now your field will contain a mix of prefixes along with the numbers, and the numbers may have to start over again from 1. In that case, you have to extract numbers from strings to increment based on string groups when you could group by prefix and just increment the max for a group instead of separating, incrementing and piecing back together.

I saw nothing in the op that said it will always be PY, but if that is the case, you would be right.

[June7]

I did say "if". And example I referenced does show building a compound value with sequence starting over each year. Just wanted to give OP something to think about. Excluding text would simplify. I could have certainly simplified my code with restructure of data. I inherited that db half built and it was already set up with this generated ID as primary key.