how to make a serial no field that resets to 0 on 1 Apr each year

[itsme1266]

I am trying to alter the primary key field in my database. It is currently a
database entry serial number field which will continue to add by one with
each record added. I want it to reset to 0 at a given point each year. Any
ideas

 

[mscertified]

So long as it is not an autonumber you can reset it at any time. However, if
it's a primary key you cannot have duplicates so you will have to clear out
all the records (or move them someplace else) before you start re-using keys.

-Dorian

 

[Tim Ferguson]

database entry serial number field which will continue to add by one
with each record added. I want it to reset to 0 at a given point each
year. Any ideas

First: look after the normalisation. You need two fields, one for the
YearNumber and another for the SerialNumber. Depending on the number of
cases you have, I guess that Integers will do for each. You can make the
Primary Key of combination of the two fields.

As long as you are entering records in a form, you can control things by
using form events like Form_InsertRecord:

set the YearNumber to the appropriate value, presumably some
manipulation of Year(Date()) and so on.

get highest number used so far for the SerialNumber for the
current year:

varTemp = DMax("SerialNumber", "MyTable", _
"YearNumber = " & txtYearNumber.Value)

if this is the first one, then the result will be Null, so
you would start from zero. If it's non-null, then you need
to add one

if IsNull(varTemp) then
txtYearNumber.Value = 0
else
txtYearNumber.Value = varTemp +1
end if


If you let your users enter their own records via queries, data tables,
MS Word, etc, then you'll find it hard (or impossible) to control them.
Real databases have triggers that can enforce this kind of rule, but Jet
does not.

Hope that helps


Tim F