conditional autonumber

[Gordon]

When I entering a new record, I want Access to assign a new value to
the primary key automatically in the following format
Ayyyy000

Where "A" stands for department, "yyyy" stands for current year, an
"0000" stands for the auto number, starting from 1

In addition, When the year changes, such as from 2004 to 2005, I wan
Access to start the "0000" part from 1 again.

Can somebody tell me if I do that and how? Thanks a lot.

 

[Gerald Stanley]

I would strongly advise that you define these 3 elements as
separate columns in your table.
The first part, 'department' will require a text box
(txtDepartment) to appear on the form used to enter new data.
The second part 'CreateYear' can be given a default value
of =Year(Date()).
The third part 'SerialNumber' should not be defined as an
AutoNumber; instead the input form should have an invisible
textbox (called for example txtSerialNumber) bound to
SerialNumber

Then in the txtDepartment's AfterUpdate eventHandler, you
can put code
txtSerialNumber = 1 + DMax ("SerialNumber" ,
"{yourTableName}" , "Department = ' & txtDepartment & "'
AND CreateYear = " & Year(Date()))

Obviously you will have to change the above names to fit in
with your table and form design but hopefully you will
understand the principle.

Hope This Helps
Gerald Stanley MCSD