Autogenerate serial number based on year

[Peter Myers]

I am creating a form that records incidents that occur at my work. I am
trying to create a field that will automatically generate an incremented
serial number based on the year. For instance, the 18th incident of 2008 will
automatically be logged as "2008/18". The catch is that the "/XX" part has to
revert back to 1 for each new year, so that the first incident of 2009 will
then be automatically logged as "2009/1".

I have tried the following;

Inserted a field into the table ("IncidentTester") named [Increment]. The
date field in the table is named [IncidentDate]. The serial number field that
I want to be autogenerated is called [IncidentNumber].

I have created a field for each of these on the form called [txtIncrement]
and [txtIncidentDate].

I have made the default value for [txtIncidentDate] =Now()

The default value for [txtIncrement] is as follows
=Nz(DMax("[Increment]","IncidentTester",DatePart("yyyy",[IncidentDate])=DatePart("yyyy",Now())),0)+1

and the default value for [IncidentNumber] is
=DatePart('yyyy',[txtIncidentDate]) & "/" & [txtIncrement]

The problem I have is that [txtIncrement] is not resetting when I change the
system clock to 2009, even though the date field (set as Now()) IS resetting.
Everything seems to be working fine aside from this. Any help in this regard
would be greatly appreciated.

 

[strive4peace]

hi Peter,

"automatically generate an incremented serial number based on the year"

using the year of the IncidentDate?

make one more field
- YrSerial, Number, Integer, DefaultValue --> null

assuming you are using a form to enter data, on the form BeforeInsert event:

'~~~~~~~~~~~~~~~~~
me.YrSerial = nz( _
dMax("YrSerial" _
, "Tablename" _
, "Year(IncidentDate)=" & Year(me.IncidentDate) _
),0) + 1
'~~~~~~~~~~~~~~~~~

whenever you want to report the calculated field that is the combination
in yyyy/x, you can do this:

year(IncidentDate) & "/" & YrSerial

I would recommend, however, for sorting purposes, that you decide how
many digits the biggest number will have. I will assume it is 4 ...
then, do this:

year(IncidentDate) & "/" & format(YrSerial,"0000")



Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.accessmvp.com/Strive4Peace/Index.htm

*
have an awesome day
*