By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,709 Members | 1,650 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,709 IT Pros & Developers. It's quick & easy.

Auto Numbering Case numbers in 06(year)-0001(case#) format

Expert 100+
P: 627
Hi Guys I am working on a database that will have a locked auto field with the following characteristics. It has to be a number that will be stored in a table, it needs to have the following format 00-0000 , in the actual table it will start with 06-0001 that being 06 is the year then - 0001 being the first case, from then on it will be 06-0002, 06-0003, 06-0004 and so on when next year hits I want the 06 to change to 07-0001 and so on. The reason I am doing this is so that I can implement this into a search criteria in the future. So when I search for 06-0001 the particular case will come up.

So number 1. What should I setup the table/field as? text/number/auto number?

Number 2. How do I make it to automatically have that value increase with each client?

Number 3. Lookin at the long term how can I make it change the year to 07 and the case number to 0001 once 2007 hits?

So what do you think guys?
Sep 29 '06 #1
Share this Question
Share on Google+
1 Reply

Expert 100+
P: 1,418
Hi CyberDyne,

So from the begining... You can't set this field number or Auto number coz there is no possibility to keep the zeros before the number...

Yeah there is a small opportunity to do it using format and validation rules but in this case I suppose that you'll have pb with the research options on this field... For exemple if you want to show all cases from 2006, so you'll type 06* and there will be an error... If it's a number You have to say >59999 and < 70000 as criteria or condition to obtain all from 2006! Is this good way to create your conditions...

AS I see You want to do this field as key of your table without dublicates?

I suggest You to introduce in this code the first 3 or 4 letters of your customer..

And maybe the customer letters is good to preced the year coz the users of the database search a lot by name ... And when then type the first 3 letters all customers with it appears and after you can see the year and additionnal information about the specified customer registration...

In this case your filter that shows all customers from 2006 is


Elegant isn't it? ;)

What is your opinion about the structure of your key?

Best regards!

Sep 30 '06 #2

Post your reply

Sign in to post your reply or Sign up for a free account.