Autonumber

[Aurora]

I am using Access 2000.
Is there a way to have Access automatically number a field
based on the Department noted. Example: Sales Department
would have a field [box#]to be numbered, beginning with
1000, 1001, 1002, 1003 etc.
Purchasing Dept would use, 2000,2001, 2003, 2004 etc.
and Eng would use 3000,3001,3002,3004 etc.

If there a way to write a formula that says something like
if [dept]= Sales, begin with 1000,go to next step
if [dept]= Pur, begin with 2000, go to next step
if [dept]= Eng, begin with 3000, go to next step
and on and on for 9 departments.

If this is not possible, please let me know. If it is,
please point me in the right direction.

Aurora

 

[Colin Halliday]

You could not use an Autonumber field for this, you would have to implement
it using some VBA code behind the form. When the dept. was selected, the
code would look up what the last number used was for that department and
then allocate the next number for this record.

However, a good design principal is to never use keys that have meaning in
any way, so it is not a good idea to do this if you are using the number as
a record key.

Colin