Changing a statement in a table to reflect a change in year

[Guest]

I have created a database that contains this field "S05-0001" The S05 is for
the year 2005 and then the last 4 is automatically generated number. I need
to change the S05 to S06 to reflect the change in year to 2006. When I just
try to change the 05 to 06 all records in the table change. How do I make
this change without affecting the previous data in the table? Thanks for the
help!!!

 

[Guest]

A couple of questions.
How is the number generated now? Is an autonumber with a format? Fields do
not store formats as it is just display.
Where did you try changing the S05 to S06?

 

[Guest]

Yes it is an autonumber format that is generated by Access. I know that
fields do not store formats, thank you for pointing that out. I changed S05
to S06 in the design view, under field properties entitled Format. As I said
previously changing the S05 to S06 in this way only caused all records
previously created to change to S06, which is not what is intended. I would
like all previous records created in 2005 to keep the designation S05 and all
new records created in 2006 to change to S06 to designate 2006 records.
Thanks for your help.

 

[Guest]

Because the S05 is not stored but only displayed it will change to whatever
you change the display to.
Another thing, an Autonumber cannot be reset each year. If you could then
you would have two 0001's.

At best, and most will advise against it, you can use an Autonumber scheme
like 20050001. Build an append query and append one record, 20050000, to the
autonumber field. Then the next year append 20060000.

If think you want to go this route then read up on the folks that say it is
a bad idea.

Then if you still want to go ahead post your request for help in how to
accomplish the changeover.

 

[John Vinson]

Yes it is an autonumber format that is generated by Access. I know that
fields do not store formats, thank you for pointing that out. I changed S05
to S06 in the design view, under field properties entitled Format. As I said
previously changing the S05 to S06 in this way only caused all records
previously created to change to S06, which is not what is intended. I would
like all previous records created in 2005 to keep the designation S05 and all
new records created in 2006 to change to S06 to designate 2006 records.
Thanks for your help.

As Karl correctly says - You Can't Do It That Way.

I would recommend that you use two fields in place of the one
Autonumber: a Text field (containing the text string "S05") and a Long
Integer - not Autonumber - field containing the sequential number.

You can automatically populate the sequential number in a Form using
code like this in the Form's BeforeInsert event:

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!txtSeqYear = "S" & Format(Date(), "yy")
Me!txtSeqNo = NZ(DMax("SeqNo", "tablename", "[SeqYear] = '" _
& Me!txtSeqYear & "'")) + 1
End Sub



John W. Vinson[MVP]