"Resetting" value back to zero for new year

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Malcolm - 04 Jan 2005 18:28 GMT

Hi, beginner here.

I have a form that automatically enters the next ID number
for new entries. The problem is, I want this ID number to
return back to 0 at the beginning of each year. For
example, 2004 had 64 entries. For 2005, the next ID keeps
coming up as 65 (of course) but I want it to be zero.
Below is the expression in the form for the ID number.
Any advice is greatly appreciated....keeping in mind this
database was created a year ago and can't be totally
revamped. THANKS!

=DMax("[ID]","tblDrawing Index")+1

Reply to this Message

Rick Brandt - 04 Jan 2005 18:56 GMT

> Hi, beginner here.
>
[quoted text clipped - 9 lines]
>
> =DMax("[ID]","tblDrawing Index")+1

If your table includes a field for the date of the record (RecordDate for
example) then change your expression to...

=DMax("[ID]","tblDrawing Index","Year(RecordDate) = Year(Date())")+1

I would also add an Nz() wrapper otherwise the very first record of each
year will fail.

=Nz(DMax("[ID]","tblDrawing Index","Year(RecordDate) = Year(Date())"),0)+1

Signature

I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

Reply to this Message

Malcolm - 04 Jan 2005 19:25 GMT

Brilliant!! Thanks, works perfectly.

>-----Original Message-----
>> Hi, beginner here.
[quoted text clipped - 20 lines]
>
>=Nz(DMax("[ID]","tblDrawing Index","Year(RecordDate) = Year(Date())"),0)+1

Reply to this Message

Linda - 07 Jan 2005 03:48 GMT

Hello from another beginner:

I have a similar situation as described here with a bit of a different
twist.

I need to generate an auto number that resets at the beginning of each
year, but that also incorporates the last 3 digits of the year in the
number.
Example: 004-001, 004-002, 004-003.....004-098. And now that it is
2005, I need it to start 005-001, 005-002, 005-003...etc.

How do I set up a field with an autonumber such as this (if it is
possible?) and where do I place such code?

Any help would be greatly appreciated.

Linda

>Brilliant!! Thanks, works perfectly.
>
[quoted text clipped - 65 lines]
>Year(Date())"),0)+1
>

Reply to this Message

Rick Brandt - 07 Jan 2005 04:02 GMT

> Hello from another beginner:

> I have a similar situation as described here with a bit of a different twist.

> I need to generate an auto number that resets at the beginning of each year,
> but that
> also incorporates the last 3 digits of the year in the number.
> Example: 004-001, 004-002, 004-003.....004-098. And now that it is 2005, I
> need it to > start 005-001, 005-002, 005-003...etc.

> How do I set up a field with an autonumber such as this (if it is possible?)
> and where do > I place such code?

> Any help would be greatly appreciated.

Use exactly the same approach and continue to use TWO fields for storage. For
display you can use...

Format(RecordDate, "\0yy-") & Format(RecordID, "000")

Signature

I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

Reply to this Message

Linda - 07 Jan 2005 04:19 GMT

>>Hello from another beginner:
>>
[quoted text clipped - 31 lines]
>Thank you so much. The database is at work, so I will try implementing it tomorrow.
>Gratefully, Linda

Reply to this Message