How do I format ID field with date prefix and have the ID restart next year? (1 Viewer)

jonarnott

New member
Local time
Today, 08:23
Joined
Nov 21, 2012
Messages
3
Hi all,

I am new to access and its intricacies!

I assume this question will have already been asked.

I have a table which will contain project information, and want the ID field, which auto assigns a unique number to each record, to be formatted like this P12-001, where 12 = the last two digits of the year the record was created. I know that if I type into the format field "P"00"-"000 i get the ID number in the format I want, but cannot figure how to get the first two zeros, to be the date format.

Further, I would like the ID numbers to restart from 1 each year, so this year this first project, P12-001 and may run through to P12-063, then the first project I enter next year to start P13-001.

Your thoughts and guidance on best practice to achieve the above would be appreciated.

Many thanks in advance
Jon
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 03:23
Joined
Feb 19, 2002
Messages
28,780
Welcome aboard:) If you are going to use a custom generated ID, you should not use it as the PK. Use an autonumber as the PK. It will end up being easier in the long run. Complex, formatted, custom ID's have a way of requiring redefinition that that will cause a mucho headache if you used it as the PK. So, to make it unique, you will need to add a unique index. That will keep you from inadvertantly generating a duplicate.

Much has been written here on how to generate your own IDs. In summary, you will use an expression similar to:

Dim ThisYear as string
ThisYear = Format(Date, "yy")
Me.ProjectID = ThisYear & Format(Nz(DMax(Right("ProjectID", 3),"tblProject", "Left(ProjectID,2) = '" & ThisYear & "'"),0) + 1, "000")

As you can see, the expression is complex and uses several nested functions. Basically it extracts the first two characters which will be the year and the last three characters which will be the sequence number. It increments the secquence number and formats it as a string so it will contain leading zeros. The Nz() is needed to take care of generating the first sequence number in a new year.

I should warn you though that this code will fail if you have more than 999 projects in a single year. Since you are expecting a max of 63, that probably won't be a problem. The other potential issue with this code is that if two people attempt to generate a new number at approximately the same time, they may end up generating the same number. If you don't have a lot of people entering data at the same time, this won't be a problem but you should be aware that it is a possibility. Generate the ID as close to the saving of the record as you can to minimize the possibility of genating a duplicate.
 
Last edited:

jonarnott

New member
Local time
Today, 08:23
Joined
Nov 21, 2012
Messages
3
Hi Pat,

Thanks for your reply, i knew this wouldnt be easy!

Looks like im gonna be doing some bedtime reading :p

OK so to clarify, i should retain the PK as autonumber, 1, 2, 3 etc but add another field, ProjectCode or similar, and have that set as unique? The expression will then deal with autonumbering itself?

Thanks for the heads up on it failing if there are more than 999 projects in a year. This database is being developed with an Architect's office in mind and i doubt even the largest practices in the world get 999 project per annum, but you never know ;-)

I note the potential issue of two people entering a new project at the same time. Again initially anyway this shouldn't be an issue as there will be an office manager / secretary to create new job numbers.

You say generate the id as close to the saving of the record, do i need to do something manually to have this genertae then? Does it not auto generate as a new record is created?

many thanks again for your help so far. Looking forward to trying this out later :D
J
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 03:23
Joined
Feb 19, 2002
Messages
28,780
Does it not auto generate as a new record is created?
No. Your code generates it. Some people like to see it appear as soon as they start entering new data in the form. In that case, you would put it in the BeforeInsert event. The problem with that is a user could start entering a record and get interupted and not finish until hours later at which time, he ID could possibly already have been taken by someone else. If I were o do this, I would do it in the Form's BeforeUpdate event. Sincethis event runs for both new and updated records, you need to determine which you have so that you only generate the ID for new records.
In the form's BeforeUpdate event:
Code:
If Me.NewRecord Then
    'generate ID
End If
An autonumber is unique so there is no need to include ProjectCode in the PK. Whether you generate a number or not really depends on how your user needs to work. If he needs something that can be used to put on external documents and file cabinents, it is probably best to generate a short, formatted code. If he doesn't need an ID for external files then you can get by with only an autonumber and just give him search fields to find the record he is looking for that way.
 

Falcone203

New member
Local time
Today, 02:23
Joined
Dec 5, 2019
Messages
8
Trying to do this in MS Access VB. Had it working in test runs then messed it all up LOL. So starting over with:

Me.ProjectID = ThisYear & Format(Nz(DMax(Right("ProjectID", 3), "tblProject", "Left(ProjectID,2) = '" & ThisYear & "'"), 0) + 1, "000")

Can't make it sequence to record 002
" + 1, "000")"

and the Format(Nz(DMax(Right("ProjectID", 3) i have to change the 3 to a 5. Run-Time states error: 'um2'. My field name is 5 characters long "RNum2"

It works for the first new blank record in my test form with a "19-001" result
second record shows "19001" as well as the third and so on

Playing with code... more to come I hope
 

Falcone203

New member
Local time
Today, 02:23
Joined
Dec 5, 2019
Messages
8
Trying to do this in MS Access VB. Had it working in test runs then messed it all up LOL. So starting over with:...

Continuing; first new blank record works fine;

"If (IsNull(DMaxRNum)) Then
ThisYear = Format(Date, "yy")
Me.RNum2 = ThisYear & Format(Nz(DMax(Right("rnum2", 5), "tblruns", "Left(rnum2,2) = '" & ThisYear & "'"), 0) + 1, "000")
Else"

the following records would double the "ThisYear" 1st 19001, 2nd 1919002.. Corrected that by deleting the fist instance of "ThisYear" in my "Else" section;

"If IsNull(Me!RNum2) Then
Me.RNum2 = Format(Nz(DMax(Right("rnum2", 5), "tblruns", "Left(rnum2,2) = '" & ThisYear & "'"), 0) + 1, "000")"
 

Dark Stranger

New member
Local time
Today, 01:23
Joined
Mar 28, 2020
Messages
10
Welcome aboard:) If you are going to use a custom generated ID, you should not use it as the PK. Use an autonumber as the PK. It will end up being easier in the long run. Complex, formatted, custom ID's have a way of requiring redefinition that that will cause a mucho headache if you used it as the PK. So, to make it unique, you will need to add a unique index. That will keep you from inadvertantly generating a duplicate.

Much has been written here on how to generate your own IDs. In summary, you will use an expression similar to:

Dim ThisYear as string
ThisYear = Format(Date, "yy")
Me.ProjectID = ThisYear & Format(Nz(DMax(Right("ProjectID", 3),"tblProject", "Left(ProjectID,2) = '" & ThisYear & "'"),0) + 1, "000")

As you can see, the expression is complex and uses several nested functions. Basically it extracts the first two characters which will be the year and the last three characters which will be the sequence number. It increments the secquence number and formats it as a string so it will contain leading zeros. The Nz() is needed to take care of generating the first sequence number in a new year.

I should warn you though that this code will fail if you have more than 999 projects in a single year. Since you are expecting a max of 63, that probably won't be a problem. The other potential issue with this code is that if two people attempt to generate a new number at approximately the same time, they may end up generating the same number. If you don't have a lot of people entering data at the same time, this won't be a problem but you should be aware that it is a possibility. Generate the ID as close to the saving of the record as you can to minimize the possibility of genating a duplicate.
Pat
I am attempting to do something similar. However, I would like the Project number to be generated with a Command. We will be entering project ideas into the database but don't want to assign a project number until the project becomes active. Could I use your code on the "On CLick" for an "Assign Project Number" command button?
Thanks
Doyle
 

theDBguy

I’m here to help
Local time
Today, 00:23
Joined
Oct 29, 2018
Messages
10,252
Pat
I am attempting to do something similar. However, I would like the Project number to be generated with a Command. We will be entering project ideas into the database but don't want to assign a project number until the project becomes active. Could I use your code on the "On CLick" for an "Assign Project Number" command button?
Thanks
Doyle
Hi Doyle. Welcome to AWF! You are replying to an old thread; but to answer your question, yes, you should be able to use a button to execute the above code. If you want to provide more details, you might consider starting a new thread. Cheers!
 

jdraw

Super Moderator
Staff member
Local time
Today, 03:23
Joined
Jan 23, 2006
Messages
13,132
Doyle,

You have joined a thread that is several years old. Although you can make customized/concocted codes for "human consumption", the more common approach is to define the fields at the atomic level (1 field 1 thing) and then combine them for display. As the thread suggests, better to use an autonumber PK in your table (that is important for the database software), and you and others can use your combined code for communication.
Good luck.

OOOoops: DBguy is so quick..
 

Dark Stranger

New member
Local time
Today, 01:23
Joined
Mar 28, 2020
Messages
10
Hi Doyle. Welcome to AWF! You are replying to an old thread; but to answer your question, yes, you should be able to use a button to execute the above code. If you want to provide more details, you might consider starting a new thread. Cheers!
will do Thanks
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom