Automatic number

[Guest]

I am using MS ACCESS 2003

I want to generate a automatic purchase number that will have the year and
sequential number; e.g., 2005-0001, 2005-0002.
Any help with code appreciated.

 

[Troy]

Store them separately. Your table will have 1 field for year, and another
for Sequential number (an Autonumber field would work). You have the choice
to display that number with x number of leading zeros. Just know that all
of your searches will only be looking at the sequential number for the
invoice number and the year for the year field for the year of the invoice.

On your form and/or report, the display is concatenated:
YearField & "-" & SequentialField

You can be more fancy with the leading zeros if you prefer:

Dim x as Integer
Dim y as Integer
Dim z as integer
Dim counter as integer
Dim strLeadingZeros as String
y = 4 'Number of leading zeros to start with
x = Len(SequentialField)
If x >= y then
'Add no zeros
Else
'Add leading zeros differences
z = y-x
For counter = 0 to z
strLeadingZeros = strLeadingZeros & "0"
Next
End if

YearField & "-" & strLeadingZeros & SequentialField

Also, technically, you don't need to store the year separately if you are
storing the full date of the invoice at creation time. Just concatenate like
this instead:

Year(InvoiceDateField) & "-" & strLeadingZeros & SequentialField

--
Troy

Troy Munford
Development Operations Manager
FMS, Inc.
www.fmsinc.com


I am using MS ACCESS 2003

I want to generate a automatic purchase number that will have the year and
sequential number; e.g., 2005-0001, 2005-0002.
Any help with code appreciated.

 

[Guest]

Thank you

 

[Joseph Meehan]

Store them separately. Your table will have 1 field for year, and
another for Sequential number (an Autonumber field would work).

The autonumber should work as long as the user is lucky or does not need
or want the number to be in sequential order. Note, that autonumbers are
designed to provide unique numbers, which may or may not be in any sort of
an order. Normally it should not be used anytime the user will see and use
the number as it tends to confuse them.

 

[Lynn Trapp]

The autonumber should work as long as the user is lucky or does not

need or want the number to be in sequential order. Note, that autonumbers
are designed to provide unique numbers, which may or may not be in any
sort of an order. Normally it should not be used anytime the user will
see and use the number as it tends to confuse them.

Also, the autonumber will not work if the OP expects the value to start over
at 1 again each year. In that case, some kind of code, probably using the
Max() or DMax() functions, will be required.

 

[Guest]

Thanks for you response! Restart at 1 each year is an issue. I read about
doing this with an Append query, but I have not figured out how to do that
yet. Since I am not a programmer, my alternative to using auto mumber is to
enter purchase order numbers manually. I'll think on that.

Thanks again