Number Roll Over

[tspresley]

Thank you for all of the help provided. I have a data base that has a
log number. The number consist of yy-xxxx (y = two digit year and x =
case number i.e. 0001). I had been manually imputting the numbers
because each set of number was assigned to a particular attorney. They
want me to create another database where they can add in their own
information, which is fine by me. How do I create a field that will
automatically change the yy at the end of the year and start the xxxx
over at 0001?

 

[Jeff Boyce]

From a design standpoint, it usually isn't considered a good idea to embed
more than one fact in one field. Your "log number" holds a year
abbreviation and a case number. Instead, use two fields to store this
information, and a query to concatenate them into the form folks like to
see.

If you use a field to store a date, you can use Year([YourDateField]) in a
query to return the year in which the record was created. You could even
use Date() as a default value for that date field on the record.

If you want a sequence number that resets at the beginning of each year,
you'll need to create a small function to do this. Check at mvps.org for
"Custom Autonumber" (it isn't really an autonumber, that's just what it's
called).

Regards

Jeff Boyce
Microsoft Office/Access MVP