Reset "0000" Number Each Year

[pushrodengine via AccessMonster.com]

I need to a 4 digit number to reset each year. The format is “0000â€.

I currently use the textbox “LastPatientID†Control Source:

=DMax("[PatientID]","tblIncidentLog")+1

but, what I would like is for the number to reset to “0001†each year.

Thanks

 

[Rick Brandt]

I need to a 4 digit number to reset each year. The format is "0000".

I currently use the textbox "LastPatientID" Control Source:

=DMax("[PatientID]","tblIncidentLog")+1

but, what I would like is for the number to reset to "0001" each year.

Thanks

The records need a field in the table that identifies the year each belongs to.
I suggest a DateTime field that holds the date and time that each record was
created because this can be handy in many other situations. I'll call this
field CreatedOn. Just give it a default value of =Now(). Then you modify your
expression as follows.

(easy to understand method)
=Nz(DMax("[PatientID]","tblIncidentLog","Year(CreatedOn) = Year(Date())"),0)+1

(more efficient method assuming CreatedOn is indexed)
=Nz(DMax("[PatientID]","tblIncidentLog","CreatedOn >=
DateSerial(Year(Date()),1,1) AND CreatedOn <
DateSerial(Year(Date())+1,1,1)"),0)+1

You need the Nz() for the first record of each year.