Primary Key Numbers

[Dave14867]

I want to be able to format the primary key field to display 4 numbers, ie., display 0001 instead of 1, 0002 instead of 2 etc., is there a way I can do this and still leave the field as auto number?

[orange]

Why? What rationale do you have for formatting the PK for display?
You can always reformat something for certain purposes eg CStr, CDate etc or Format().
Primary key is meant for the DBMS, not the client/user per se.

Good luck

[Dave14867]

I want to display it because the form it represents is formatted that way. It is just easier than creating a new column with essentially the same number.

[aytee111]

In no time at all you will reach the 9999 limit. NEVER use autonumber fields to have meaning. You'll find that it isn't that much extra work to create an extra field under your control.

[aytee111]

In another thread, ssanfu displays this link to how to use autonumbers.

[ssanfu]

Also see Microsoft Access Tables: Primary Key Tips and Techniques

[Dave14867]

Here is my problem, this is the number I am working with, 50-09-0001, the 50-09 is a constant, the last 4 digits increment with every new instance of a document, it will take literally years to reach 50-09-9999 so that doesn't concern me. The table I currently have (imported from an Excel file) has a standard primary key of 1,2,3 etc that matches the last 4 digits (1 =50-09-0001, 150=50-09-0150, etc). I have entered a new field like others have suggested but I don't know how to add the leading "0's".

I then would like to have this field automatically populated based on the primary key #. Does that make sense?

Your help and guidance is greatly appreciated.

[orange]

Not really. 50-09-0001 is not a number. I would call it a custom code made up of a dash and digits. It is composed of 2 or 3 parts separated by "-"(dash). It means something to you and others in your organization. Too many people attempt to put too much intelligence into their custom codes.
Most designers would advise you to use atomic fields. That is fields that have a single fact in each field eg. Firstname, Lastname rather than CustomerName composed of both First and Last name.

You can create a "sequence" (incremental numeric values) using DMax(current value) +1.

I suggest you research Dmax() +1 and if you really need the code, then add your prefix (a string "50-09") to a formatted Dmax() + 1
for the code. You will need to format the number to get your leading 0's, and format will result in a string.
Iwould use a separate autonumber field as PK. Others may have a different view.

Good luck.

[Dave14867]

Orange,

Your are correct, my mistake, it is a document number and only the last 4 digits are what I am concerned with, they are significant and sequential, each new document has a different 4 digit ending. I will take a look at what you suggest, it seems like it should work great, I just have to figure out how to do it, thanks for the suggestion.

[RuralGuy]

Paul has a good description here: http://www.baldyweb.com/CustomAutonumber.htm

[orange]

I agree with RuralGuy see the link to Paul's site.
Here's another https://www.599cd.com/tips/access/in...r-own-counter/

[orange]

Dave,

Attached is an article that may put some thoughts/concepts/approaches into context.
It has been referenced by others in the past, and I think it is well done.

Good luck.