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

[jonarnott]

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

 

[jonarnott]

Hi Pat,

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

Looks like im gonna be doing some bedtime reading

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
J

 

[jdraw]

As part of your reading, you might want to read these articles

Database principles
http://forums.aspfree.com/attachment.php?attachmentid=4712&d=1201055452

Autonumbers http://www.utteraccess.com/wiki/index.php/Autonumbers

Good luck with your project.

 

[jonarnott]

Thanks jdraw

Will get reading asap

 

[Falcone203]

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]

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]

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]

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]

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]

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

 

[theDBguy]

will do Thanks

I'll try to keep an eye out for it. Cheers!