how to increment a counter

[DBrack]

We have a table whose text column value has to be reset to 0001 at the end of year. This column value is usually incremented everytime a form is submitted (that's when a database record is entered into another table). Therefore, it becomes 0002, 0003 etc ..

After the end of year 2006, i.e this counter should be reset to 0001.

Appreciate if someone could suggest a way to do this

Thanks.

 

[snyperx3]

is there a date field in the same table to use as a reference?

you can increment the textfield using this method (this will not account for the reset at a new year):

Count = right("000" & Cint(DMax("Count","tblTable"))+1,4)

-Pete

 

[ProgramError]

I would use an admin table with a field to store a year variable. eg 2005
then use the following in the data entry form


Dim Yearstore as Date
Yearstore = Datepart("yyyy",DLookup("DateField", "Admin"))

If datepart("yyyy",date())>yearstore then
counter = 0
yearstore = date() ' not sure how to put this back into the date field of the admin table though....
end if
counter = counter + 1

and use 0000 in the format property of the textbox to display the 0001

Hope this helps

Ian Mayor (UK)
Program Error
Programming is 1% coding, 50% error checking and 49% sweat as your application bombs out in front of the client.