Autonumber

[Aurora]

I am using Access 2000

I want an autonumber field to begin with the "current
year - (4-digit) autonumber". Ex: 03 - 1234. In the
format area of the autonumber field I entered yy - &&&&.
I also tried "yy - "&&&& but neither of these seem to be
working correctly. At first I got a year of 08. I
checked my computer date and it is correct.

Can anyone help me put in the correct code.

Thank you - Aurora

 

[Cheryl Fischer]

Aurora,

If you want to have a primary key for your table which is made up of:

YY - #### or YYYY - ####

that field cannot be assigned the DataType of AutoNumber in your table's
design view. The format you desire will need to be a Text Field and you
will have to write some code which will (before a new record is saved)
determine the maximum value of the #### part of your field for the
particular year, Add 1 to it, and then write the Year value plus the new
#### to the new record. AutoNumber fields have "no internal intelligence";
they are simply numbers created by the database which are helpful in linking
Forms to SubForms and Parent Tables to Child Tables in one-to-many
relationships, etc. In fact, most experts recommend that they not be viewed
on a form.

If you need to know the year record was added to a database, try creating a
field such as YearAdded. Make its DataType a Number and its FieldSize an
Integer. In the Default Value property of the table's design insert:

=Year(Date())

Or, preferably, create a field named DateAdded, make its DataType a
Date/Time, and set its Default Value to =Date(), for date only or =Now() for
date and time. Then if you need to know the order in which records were
added to your database, you can sort by the DateAdded field. In queries,
you can use that field to find records added in a particular year by using
the Year() function:

Year(DateAdded)=2003

If you must have a custom numbering scheme, there are many out there and you
can go to http://groups.google.com and search on: Access custom numbering.

 

[Rick Brandt]

I am using Access 2000

I want an autonumber field to begin with the "current
year - (4-digit) autonumber". Ex: 03 - 1234. In the
format area of the autonumber field I entered yy - &&&&.
I also tried "yy - "&&&& but neither of these seem to be
working correctly. At first I got a year of 08. I
checked my computer date and it is correct.

Can anyone help me put in the correct code.

What you're attempting is not possible with an AutoNumber Just use an
additional Date field and then you can use an expression to *display* data
from both fields in a single control.

=Format([YourDateField], "YY-") & Format([YourAutoNumber], "0000")

If you use this on your forms and reports the users don't even have to know
that it's not really a single field. You can even put the expression in a
query based on your table and then base all reports and forms on the query.
Then you only have to write the expression one time.

 

[mhill31]

Thank you from someone that didn't post the question but needed the
answer. Both answers were very helpful.