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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
"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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
"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
|
|||
|
|||
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 | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
"Back" button from custom shows causing problems | Gary A. | Powerpoint | 1 | December 1st, 2004 03: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 |