Table with Autonumber and Need Date Code

[tpeter]

I have a table named tblProjectNumber that has a autonumber field. In design view under the format property I have "NC07-"00000, this returns the a new project number with the text in front of it. What I would really want is for the text the change dynamically with the year. So an example would be NC07-00000 for project numbers created this year but I will manually have to change it for next year and the all of the previous project numbers will change accordingly. I have tried "DateSerial(Year(Date())-"00000 , Date("yy") and I can't get it tto work.Any help would be great.

Tim

 

[neileg]

Few points
1. Autonumbers are are poor choice for a number that has meaning. You certainly can't guarantee no gaps in the sequence, and occasionally the number goes haywire. There are ways of creating your own sequential numbers using DSum(). Do a search.
2. This type of dynamic formatting can't really be done at table level. You should do it in a form. It's prefereable to store data in it's raw form in tables and apply formatting in the form or report.
3. Where you have a composite numbering system, you should store the component parts in their own fields and concatenate them when you need to. This makes it much easier to have different rules for the different components, and makes searching and filtering much easier.
4. Don't use your numbering system as a Primary Key. Set up an autonumber primary key and keep this away from the users.