Go Back   Access World Forums > Microsoft Access Discussion > Tables

 
Reply
 
Thread Tools Rating: Thread Rating: 50 votes, 5.00 average. Display Modes
Old 11-20-2012, 05:58 PM   #1
jonarnott
Newly Registered User
 
Join Date: Nov 2012
Location: Dundee
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
jonarnott is on a distinguished road
Question How do I format ID field with date prefix and have the ID restart next year?

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 is offline   Reply With Quote
Old 11-20-2012, 06:32 PM   #2
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,303
Thanks: 15
Thanked 1,597 Times in 1,517 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: How do I format ID field with date prefix and have the ID restart next year?

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.
__________________
Bridge Players Still Know All the Tricks

Last edited by Pat Hartman; 11-20-2012 at 06:38 PM.
Pat Hartman is offline   Reply With Quote
Old 11-21-2012, 02:26 AM   #3
jonarnott
Newly Registered User
 
Join Date: Nov 2012
Location: Dundee
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
jonarnott is on a distinguished road
Re: How do I format ID field with date prefix and have the ID restart next year?

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
J

jonarnott is offline   Reply With Quote
Old 11-21-2012, 07:05 AM   #4
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 12,273
Thanks: 96
Thanked 2,030 Times in 1,977 Posts
jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light
Re: How do I format ID field with date prefix and have the ID restart next year?

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

Database principles
http://forums.aspfree.com/attachment...2&d=1201055452

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

Good luck with your project.
jdraw is offline   Reply With Quote
Old 11-21-2012, 10:18 AM   #5
jonarnott
Newly Registered User
 
Join Date: Nov 2012
Location: Dundee
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
jonarnott is on a distinguished road
Re: How do I format ID field with date prefix and have the ID restart next year?

Thanks jdraw

Will get reading asap
jonarnott is offline   Reply With Quote
Old 11-24-2012, 12:53 PM   #6
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,303
Thanks: 15
Thanked 1,597 Times in 1,517 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: How do I format ID field with date prefix and have the ID restart next year?

Quote:
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.

__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Reply

Tags
date , field , format , prefix , unique

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
how to format date from year month and day? Jeff06 Queries 3 08-20-2007 07:15 AM
Format Date to show Month & Year only Rats Forms 4 10-23-2006 11:30 PM
Date Format/Right/Year = Dirty Canis lupus Forms 2 04-05-2005 10:55 AM
Format Date as Month/Year? bfdeal31 General 2 07-02-2002 01:23 PM
[SOLVED] How to setup auto number with year prefix that changes each year fvicary Forms 1 04-06-2000 09:43 PM




All times are GMT -8. The time now is 05:56 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World