Autonumbering....Easy Question

[Brad]

I am currently autonumbering a field in my table with the
format "03-0000". The "03" was for year 2003 and the "-
0000" was the autonumbering.

My table is up to number 03-0151. Now that it is 2004 I
want to reset the autonumber to "04-0000" so it will start
over at number one but I don't want to erase or lose the
151 records I have in there from 2003.

How do I change the autonumber to 04-0000 without losing
my 03-0001 to 03-0151 records????

 

[Casey]

Make a new table?

A bigger question, why are you storing the year in the
autonumber field. Why not have a different field for year?

Just trying to help since no one at MS is willing.

Casey

 

[Scott McDaniel]

If you are using the builtin AutoNumber datatype then ... you aren't
actually saving the Autonumbered field in this format, you are simply
viewing it in this fashion. If you look at the underlying data, you'll see
that the Autonumber field is second part of this expression (the part after
the dash). You cannot reset this value without deleting all data in the
table and compacting the database.

If you're NOT using the builtin Autonumber datatype, then you'll need to
post the code that builds this value.

 

[Tim Ferguson]

My table is up to number 03-0151. Now that it is 2004 I
want to reset the autonumber to "04-0000" so it will start
over at number one but I don't want to erase or lose the
151 records I have in there from 2003.

First of all, you don't want to store two bits of information (YearCreated,
SerialNumber) in the same field. Check up on First Normal Form.

Second of all, if you care what value an Autonumber has, then you should
not be using an Autonumber.

In summary: use two fields, one for the YearCreated field (this may be
redundant if you already have a DateCreated field), and the other for the
SerialNumber. You will need to use a form and a little bit of programming
to provide the value for the SerialNumber -- try googling for Access Custom
Autonumbers, or start with Dev's site ,<http://www.mvps.org/access>

Hope that helps


Tim F

 

[John Vinson]

Make a new table?

A bigger question, why are you storing the year in the
autonumber field. Why not have a different field for year?

Good question - one that I was about to ask myself.

Just trying to help since no one at MS is willing.

Note that this newsgroup is NOT staffed by Microsoft employees; it's a
peer to peer newsgroup, with volunteers like me (or you!) answering
questions as a public service. Some Microsoft employees may post
answers from time to time but if so they're doing it on their own
time.

See my reply elsethread.

 

[John Vinson]

I am currently autonumbering a field in my table with the
format "03-0000". The "03" was for year 2003 and the "-
0000" was the autonumbering.

My table is up to number 03-0151. Now that it is 2004 I
want to reset the autonumber to "04-0000" so it will start
over at number one but I don't want to erase or lose the
151 records I have in there from 2003.

How do I change the autonumber to 04-0000 without losing
my 03-0001 to 03-0151 records????

This is called an "Intelligent" key... not a compliment! Storing two
pieces of information in one field, redundantly, is generally a Bad
Idea, and should be done only for compatibility with a deeply
entrenched existing numbering system.

An Autonumber will NOT work here. An Autonumber is just a long
integer, almost guaranteed to be unique, which counts up from 1 to
over 2 billion. To get the result you want I'd suggest two Integer
fields, one for the year and one for the incrementing number; you'll
need some VBA code to increment the latter.