Count or Sum entries by year

[Guest]

Have a form that works right now using a autonumber.
When the user enters the date then it generates a text string for the
primary object.
The primary field is not the autonumber field (indx) in the table is gets
this value.

Is there a way to count or sum the number of entries in the table based on
the year so the index resets when the new year starts? Then I can apply this
to the afterupdate object (date) and no longer have the need for the
autonumber field. I could declare this value and concate the value with the
string creating a unique value.

EX: CA06-001, 002, 003, but the new year 2007 will return CA07-004 and i
would rather have it return CA07-001.

If you need further explaination please let me know.

Thank you for your assistance

 

[Douglas J Steele]

DCount("*", "MyTable", "WhatYear = " & Year(DateField)) will tell you how
many entries there currently are with the same year in DateField.

You really should store CA06-001 as 3 separate fields: it's a violation of
database normalization principles to store multiple pieces of data in a
single field. If you need CA06-001 for display purposes, create a query that
has a calculated field that creates that value, and use the query wherever
you would otherwise have used the table.