Custom Auto-Number Sequence

[Guest]

My database contains a specific record number XXXX-YYY.
XXXX = Year
YYY = Record Number (starting with 001)

How would I go about formatting the field to output that format automatically?
In addition, what do I need to do to reset YYY to 001 when the next year?

 

[Rick Brandt]

My database contains a specific record number XXXX-YYY.
XXXX = Year
YYY = Record Number (starting with 001)

How would I go about formatting the field to output that format
automatically? In addition, what do I need to do to reset YYY to 001
when the next year?

The easiest, most efficient, and most flexible way to do this is to have two
fields [RecordDate] and [ID]. RecordDate is a DateTime with a default value of
Now() so it is easy to set up and no code is required to populate it. There are
numerous situations where knowing exactly when a record was created is
beneficial so this provides that at the same time.

Once you have a [RecordDate] then assignment of the other field [ID] is very
simple. As each record is inserted you just need to find the highest ID value
that was created in the current year and add 1 to it. The BeforeUpdate event it
best for this...

If Me.NewRecord Then
Me![ID] = Nz(DMax("ID", "TableName", "Year(RecordDate) = Year(Date())"), 0)
+ 1
End If

On your forms and reports you can display these two fields as one identifier
with an expression of...

=Format([RecordDate], "YYYY\-") & Format([ID], "000")

....that will give you the format you asked for.

 

[BruceM]

To what Rick has written I will add that you will need to use something
other than the RecordNumber as the PK, since it will repeat from year to
year, and that you can wrap the Format function around the incrementing code
to get the 001, 002, etc. format.

 

[Mike]

Good Morning Rick.
Your posting below was very helpful. However, I would like to know if there
is a way of advancing it so that all of the digits (such as 08 - 001) will
store into a table? Also, what I would like to be able to do is not have a
repeat of the same number if the highest numbered record is deleted. For
example; I have 100 enteries ranging from 08-001 to 08-100. If I delete the
last entry, I would want it to be 08-101, not pick up at 08-100 again.
If there is an alternative way of designing your post below in order to
achive all of what I am looking for, please explain.
Also, just as a little extra note for anyone reading this post, I have
posted some of my problems before on this site and everyone has been very
helpful. Thank You All For Your Assistance. I am not new with Access, but I
dod not know all the ins and outs of it yet. Hopefully some day though.

Thanks,
Mike
--
Mike

My database contains a specific record number XXXX-YYY.
XXXX = Year
YYY = Record Number (starting with 001)

How would I go about formatting the field to output that format
automatically? In addition, what do I need to do to reset YYY to 001
when the next year?

The easiest, most efficient, and most flexible way to do this is to have two
fields [RecordDate] and [ID]. RecordDate is a DateTime with a default value of
Now() so it is easy to set up and no code is required to populate it. There are
numerous situations where knowing exactly when a record was created is
beneficial so this provides that at the same time.

Once you have a [RecordDate] then assignment of the other field [ID] is very
simple. As each record is inserted you just need to find the highest ID value
that was created in the current year and add 1 to it. The BeforeUpdate event it
best for this...

If Me.NewRecord Then
Me![ID] = Nz(DMax("ID", "TableName", "Year(RecordDate) = Year(Date())"), 0)
+ 1
End If

On your forms and reports you can display these two fields as one identifier
with an expression of...

=Format([RecordDate], "YYYY\-") & Format([ID], "000")

....that will give you the format you asked for.

 

[Mike]

Reason(s) are as follows:
In our community, we have about 680 homes. We are the security company for
the community. Basically we responde to calls just like your local police
department does. Alarms, medicals, complaints, etc.
Whenever your local police department responds to a call, a dispatch number
is created and it will generally start with the first set of digits as being
the year and the last set of digits as being the number of calls handled to
date for that particular year i.e; 2008-0100 would be the 100th call for that
year. The last set of numbers would go up (1) digit with each call that was
added. The number would start back at 0001 for the first call of the next
year. This way, I do not have to put in a "new" table for each year.
The above example would be preferred to be stored into the same table. Or
is it considered "best" to only have it show on forms, reports, and quires?
We would also sometimes "Cut" and "Paste" a call or "record" from one table
to another. Such as an "Out of Town" table and a "Return" table where a
resident goes out of town then returns at a later date. When a resident goes
out of town, it is entered into the Out of Town form. When they return, there
record is "cut" and "pasted" into the Return table. When a new record is
added to the Out of Town form, a new ID number is used, not one that has been
removed while cutting and pasting into a different table.
In both of the above examples; I am currently using an AutoNumber. But
right now, at the start of each year, I have to put in a new "Out of Town"
and "Return" table. As far as the "Dispatched Calls" table goes, we are
curently up to call number 10,000 since 2005 with no way of distingushing the
year without looking at the dates and no way of starting the Call #'s back to
0001 for the first call of the year.
I hope this is helpful, sorry for being long winded.

Thank You,
Mike