Reset a number back to 1 at the beginning of the year

[Guest]

I have a project I am working on where the client requires a document number
to increment by 1 each time and the prefix of the number is the year (ex.
05-00001, 05-00002, etc.) I need to program the number on the form to roll
back to "06-00001" at the beginning of next year (And subsequent years).
Incrementing the number isn't a problem...rolling back is. (I'm not using the
Autonumber for this)

HELP!

 

[Guest]

This question gets answered a couple of times per week.

Dim varNextNum as Variant

varNextNum = DMax("[DocNumber]", "MyTableName", _
"Left([DocNumber], 3) = '" & Format(Year(Date), "yy-") & "'") + 1

If IsNull(varNextNum) Then
varNextNum = Format(Year(Date), "yy-") & "000001"
End If

 

[Tim Ferguson]

I have a project I am working on where the client requires a document
number to increment by 1 each time and the prefix of the number is the
year (ex. 05-00001, 05-00002, etc.) I need to program the number on
the form to roll back to "06-00001" at the beginning of next year (And
subsequent years).

You need two columns: YearNumber and SerialNumber. It's then a trivial
business to use a DMax() call to find the largest number allocated for the
particular year. It's even easiser to contatenate the fields on forms and
reports. Google for "Access Custom Autonumbers" for more details.


Tim F

 

[Guest]

I disagree, Tim. If you will read my response, it is easy enough to keep it
in one coulmn. Using 2 columns creates more work when creating forms,
reports, queries, etc.

 

[Douglas J. Steele]

While it may be easy to do with one column, realistically it's a violation
of database normalization principles.

Each field should contain only a single atomic piece of data. 05-001,
05-002, 06-001 etc. stores two pieces of information in a single field.

 

[Guest]

I agree that each field should contain an atomic piece of data; however, It
appears to me that this is one piece of data that identifies a specific
document. Neither has any meaning without the other.

 

[Rick Brandt]

I agree that each field should contain an atomic piece of data;
however, It appears to me that this is one piece of data that
identifies a specific document. Neither has any meaning without the
other.

Also, it should be pointed out that the criteria necessary for an all-in-one
field...

Left([DocNumber], 3) = '" & Format(Year(Date), "yy-") & "'")

....is inefficient because it cannot utilize an index. If the table is large
a full table scan could avoided by keeping the data in separate fields so
that normal date criteria could be used on the date field.

 

[Guest]

First, there is no date field. It would need to be either Long (or Integer)
or Text.
Indexing on the two fields would not be that efficient because of
redundancy. There would be a large number of duplicate year fields, and
duplicate document fields.
05 000001
05 000002
06 000001
06 000002

So duplicates would have to be allowed. Neither of these fields would be
meaningful on their own and would also violate pure database normalization
rules because of the redundancy.
I think the extra processing necessary every time you need to reference a
document would outweigh the use of the code I posted.

I agree that each field should contain an atomic piece of data;
however, It appears to me that this is one piece of data that
identifies a specific document. Neither has any meaning without the
other.

Also, it should be pointed out that the criteria necessary for an all-in-one
field...

Left([DocNumber], 3) = '" & Format(Year(Date), "yy-") & "'")

....is inefficient because it cannot utilize an index. If the table is large
a full table scan could avoided by keeping the data in separate fields so
that normal date criteria could be used on the date field.

 

[Rick Brandt]

First, there is no date field. It would need to be either Long (or
Integer) or Text.
Indexing on the two fields would not be that efficient because of
redundancy. There would be a large number of duplicate year fields,
and duplicate document fields.
05 000001
05 000002
06 000001
06 000002

So duplicates would have to be allowed. Neither of these fields
would be meaningful on their own and would also violate pure database
normalization rules because of the redundancy.
I think the extra processing necessary every time you need to
reference a document would outweigh the use of the code I posted.

Well if I were doing it there would be a date field. I would have a full
DateTime field and a Long Integer field and would have indexes on eqch of them.
Then the calculation for next number could be set up to use both indexes.

 

[Guest]

Neither of those indexes would be unique. It would also be almost impossible
to get a match on the numbering. The prefix is year only. The month and day
would only confuse the issue. I would not design a system where it takes two
fields to carry one piece of data.

I think will will have to agree to disagree on this.

 

[Rick Brandt]

Neither of those indexes would be unique.

Why would uniqueness be a requirement? My point was only that if you apply
criteria in a domain function against an expression then you always get a table
scan. If you instead apply criteria to indexed fields then the indexes can be
taken advantage of.

It would also be almost impossible
to get a match on the numbering. The prefix is year only. The month and day
would only confuse the issue. I would not design a system where it takes two
fields to carry one piece of data.

Most records can benefit from a CreatedOn DateTime field. This same field could
be utilized to produce the proper prefix numbering merely by using the Format()
function.

 

[Guest]

Are you saying that a non unique index would give you better performance a
domain expression?

 

[Guest]

Rick,

I have thinking a lot about this discussion. I save advantages and
disadvantages to both of our ideas. I have come up with a concept that uses
one field, but also has the advantage of being able to use an index where the
values are unique.

lets say we want to have the year as two digits, a dash, and a three digit
number. As we both know, how we carry data and how we present the data are
not necessarily the same, so. let's take the year, multiply it by 1000 (one
zero for each unique number). So the first number would be 05-001. To
create a unique number:
(Clng(Format(Year(Date),"yy")) * 1000) + 1
will result in 5001
Then to display it:
Format(x,"00-000")
will result in 05-001

Now, to fin the maximum for the current year:

lngFindValue = (Clng(Format(Date,"yy")) + 1) * 1000
' Now we have 6000
DMax("[DOC_NUM]", "MyTable", "[DOC_NUM] < " & lngFindValue)

I would appreciate your thoughts on this approach.