Reset Number each year

[George]

Dear friends, your valuable help is needed again,

I have a table - T_Issues with the following fields:

RadioSN (text) - Primary key
DateIssued (date) - Primary key
EmployeeCode (text) - Primary key
SN - Number Integer

On a form, based on this table I need to increment the SN by one (this is
easy, using the dmax +1).

My difficulty is to reset the SN each year, e.g. I have recorded 200 records
in year 2007 and therefore the greatest SN is 200. Now, at the beginning of
2008 (and following years) I need to reset the SN back to 1 and then to be
increased by 1.

Thanking you in advance,

GeorgeCY

 

[roger]

your dmax needs to only count this year's records, You have a date field, you
just need to filter your dmax statement somthing like:

where the year of dateIssued equals the year of now

DMax("[SN]", "YourTable", "DatePart("yyyy", [DateIssued]) =
Datepart("yyyy",Now()")

(I may not have the punctuation right)

 

[Dennis]

Well, what I do is have a little table that has the date and the next "serial
number" I'm going to use. Then, in the BEFORE UPDATE event, I check today's
date (or YEAR in your case) against the one in the table. If my CURRENT DATE
(or year) is greater than the one in the table, I reset the SN to 1 and set
the date (or year) in the table to the current one. Works like a charm.

Yes, it's a little kludgy, but I can look at the table and see at-a-glance
what the current date/SN information is.