Autonumber

[kyhigh]

I read with interest and can use a reply Mr. Vinson made in regards to having
a number field automatically increase. I have a text field with this type of
string, NCR 08-011. The last number can be reset by the previous answer, but
here is my question. How can I have the 08 change to 09, then 10, etc at the
new year? I would also like to reset to 001 so that if the last string reads
NCR 08-033, it resets to NCR 09-001 and then counts up by one each new entry.
Any chances for this?

 

[Arvin Meyer [MVP]]

I read with interest and can use a reply Mr. Vinson made in regards to
having
a number field automatically increase. I have a text field with this type
of
string, NCR 08-011. The last number can be reset by the previous answer,
but
here is my question. How can I have the 08 change to 09, then 10, etc at
the
new year? I would also like to reset to 001 so that if the last string
reads
NCR 08-033, it resets to NCR 09-001 and then counts up by one each new
entry.
Any chances for this?

I use a reset button at the beginning of the year.
My Numbers are saved, and include the current year. The reset button, sets
my seed table back to 1 so it starts the new year. The button is disabled by
default, but becomes enabled for the first 5 days of the year. I'm not sure
how John set up your increment, but mine uses a table with 1 record to store
the next number. It is incremented as each new record is created. Because
multiple people can be creating new records at the same time, I avoid any
conflict by grabbing the available record and instantly incrementing it by
1. As long as 2 users don't create a new record within the same 150 to 200
milliseconds, there is never a problem.

Sub Form_Open(Cancel As Integer)

If Date >= CDate("1/1/" & Year(Date)) And Date <= CDate("1/5/" &
Year(Date)) Then
Me.cmdReset.Enabled = True
Else
Me.cmdReset.Enabled = False
End If

End Sub

 

[Jeff Boyce]

Access "Autonumber" datatype is numeric only, is intended to provide a
unique row identifier, is not necessarily sequential, won't "start over" at
each new year, cannot be "embedded" within a longer string like yours, and
is generally unfit for human consumption.

That said, you can certainly present the user with something that looks like
"NCR 08-011" without having to store that!

If "NCR " is ALWAYS the first four characters (i.e., you never have "IBM "
or "ATT "), don't bother storing it!

If the "08" is ALWAYS the last two digits of the current year (e.g., 2008),
you can use the Right(Year(Date),2) to get that!

If you want a sequence number, you need a field to hold that -- take a look
at mvps.org/access for "custom autonumber" to get some ideas how you can put
together a procedure that generates a 're-setting sequence number'.

When you have all those (separate) pieces stored (separately - one fact, one
field), use a query to concatenate them together for display (in forms, in
reports) purposes.

Regards

Jeff Boyce
Microsoft Office/Access MVP