Can I make an Auto Number start with zero

T

Tami

I am trying to make an auto number start with zero. The number is a warrant
number, which is the year, then a five digit number. I already have an
expression set up to automatically update the number by one and place the new
number in a form when a box is clicked. This number is in a table by itself.

The problem is the number should start with 0700001 and then change to
0700002 and so on. What is saved in the number table is 700001. I can add
the zero in after clicking the box in the form, but I would like it to be
automatic. Can I make an automatic number start with zero?

Thank you. Tami
 
R

ruralguy via AccessMonster.com

Hi Tami,
AutoNumbers are not for human consumption! If the value is important to you
or anyone then create your own numbering and incrementing system and do not
use an AutoNumber.
 
K

KARL DEWEY

Numbers do not have leading zeros -- only text has leading zeros.
Use a text box with format as 0000000 and increment it by pulling the
maximum value of the field plus one.
 
J

Jeff Boyce

Tami

As Karl points out, leading zeros mean nothing to numbers. So let me ask,
are you planning to "do math on" (add, subtract, multiply, divide) your
"numbers", or are they really just a "code"? If you won't be doing math on
them, use, as Karl points out, the "text" datatype instead. (Hint: is a US
ZipCode a "number"?)

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
J

Jack Cannon

Tami,

As has been pointed out autonumbers are generally not intended as
information for the user. However you can use them in certain situations.
You will have to convert the number to a string to accomplish your objective.

Try the following:

Dim strWarrantID As String

strWarrantID = Right(CStr(Year(Date)), 2) & Right("00000" & CStr(WarrantID),
5)

It should continue to work when you enter the year 2010 and as long as you
do not exceed 99,999 warrants per year. A note of caution though. If the
magnitude of the number is intended to indicate the volume of warrants in a
single year then it will not be useful as you enter a new year. You will
have to do something different even though each warrant number will be
unique. Also if a warrant is deleted a gap will appear within the sequence.

Jack Cannon
 
T

Tami

Hi Jack,

Thanks for the help. Where would I add that in the expression I already
have to increase the number by one? Here it is:

Dim OCA_Number As Double
' Get the field value
OCA_Number = DLookup("[OCA_Number]", "[OCA_Number]")
' Update the table, add 1
CurrentDb.Execute ("Update OCA_Number Set OCA_Number=" & OCA_Number + 1),
dbFailOnError
' Display the value in the form
Me.[New_OCA_Number] = OCA_Number + 1

Thanks for the help.

Tami
 
J

Jack Cannon

Tami,

Your comment within the code implies that you are only attempting to
"Display the value in the form". If that is indeed the case then you should
have a label on the form named "New_OCA_Number". It should not be a field in
your database record. I would also question whether OCA_Number should be
declared as a Double since you are limited by definition to 99,999 entries
per year. I would declare it as Long, but this minor point does not affect
the actual operation. Also if if you are incrementing OCA_Number within
your code it is not actually an autonumber.

If I do understand your objective then replace the control
"[New_OCA_Number]" with a label named "New_OCA_Number". Then the last line
of the code that you provided should read:

Me.New_OCA_Number.Caption = Right(CStr(Year(Date)), 2) & Right("00000" &
CStr(OCA_Number + 1), 5)

That should work for you.

Jack Cannon


Tami said:
Hi Jack,

Thanks for the help. Where would I add that in the expression I already
have to increase the number by one? Here it is:

Dim OCA_Number As Double
' Get the field value
OCA_Number = DLookup("[OCA_Number]", "[OCA_Number]")
' Update the table, add 1
CurrentDb.Execute ("Update OCA_Number Set OCA_Number=" & OCA_Number + 1),
dbFailOnError
' Display the value in the form
Me.[New_OCA_Number] = OCA_Number + 1

Thanks for the help.

Tami

Jack Cannon said:
Tami,

As has been pointed out autonumbers are generally not intended as
information for the user. However you can use them in certain situations.
You will have to convert the number to a string to accomplish your objective.

Try the following:

Dim strWarrantID As String

strWarrantID = Right(CStr(Year(Date)), 2) & Right("00000" & CStr(WarrantID),
5)

It should continue to work when you enter the year 2010 and as long as you
do not exceed 99,999 warrants per year. A note of caution though. If the
magnitude of the number is intended to indicate the volume of warrants in a
single year then it will not be useful as you enter a new year. You will
have to do something different even though each warrant number will be
unique. Also if a warrant is deleted a gap will appear within the sequence.

Jack Cannon
Click to expand...
Click to expand...
 
Top