"Resetting" value back to zero for new year

[Malcolm]

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

 

[Rick Brandt]

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

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

 

[Malcolm]

Brilliant!! Thanks, works perfectly.

-----Original Message-----

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

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

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


.

 

[Linda]

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.

-----Original Message-----
Malcolm wrote:

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

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


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


.

 

[Rick Brandt]

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")

 

[Linda]

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

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



Thank you so much. The database is at work, so I will try implementing it tomorrow.
Gratefully, Linda