A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » New Users
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

"Resetting" value back to zero for new year



 
 
Thread Tools Display Modes
  #1  
Old January 4th, 2005, 07:28 PM
Malcolm
external usenet poster
 
Posts: n/a
Default "Resetting" value back to zero for new year

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
  #2  
Old January 4th, 2005, 07:56 PM
Rick Brandt
external usenet poster
 
Posts: n/a
Default

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


  #3  
Old January 4th, 2005, 08:25 PM
Malcolm
external usenet poster
 
Posts: n/a
Default

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


.

  #4  
Old January 7th, 2005, 04:48 AM
Linda
external usenet poster
 
Posts: n/a
Default

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

Malcolm wrote:

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


.





  #5  
Old January 7th, 2005, 05:02 AM
Rick Brandt
external usenet poster
 
Posts: n/a
Default

"Linda" wrote in message
...
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")




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


  #6  
Old January 7th, 2005, 05:20 AM
Linda
external usenet poster
 
Posts: n/a
Default



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



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







 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
"Back" button from custom shows causing problems Gary A. Powerpoint 1 December 1st, 2004 04:47 PM
Add a FIXED MONTH and DAY with the CURRENT YEAR texcel General Discussion 1 June 23rd, 2004 01:36 AM
Back Cross Reference Sukhjeet General Discussion 1 May 29th, 2004 04:57 PM
Combining two queries or two different table on a same report . sha Setting Up & Running Reports 7 May 28th, 2004 10:41 AM
Revert Back to Menu Dave Y Powerpoint 3 May 26th, 2004 02:21 PM


All times are GMT +1. The time now is 05:42 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.