Resetting sequenced number

[jepoysaipan]

Hi,

I stumbled upon a site (http://www.access-programmers.co.uk/forums/archive/index.php/t-146119.html) and have this code tested:

...

As to your problem.

Create two fields in your table:
TheYear - long
TheSequence – long

Then enter this code in your form's BeforeInsert
Me.TheYear = CLng(DatePart("YYYY", Date()))
Me.TheSequence = Nz(DMax("[TheSequence]", "YourTableName", "[TheYear] = "&Me.TheYear),0) +1

That’s all the code you need. When a new year starts the DMax function returns a Null, which the Nz function changes to a 0. Then it just adds 1 to get the next value.
...

Now the question is if the year changes the sequenced number doesn't reset back to 0.

Can someone trace this one? Co'z on my previous post regarding the autonumber reset, this will be the solution to it, I will just change the datepart to "m" to reset its value every month.

Please guys I really need your help.

Jeff

 

[neileg]

If the year changes, then this piece of code

DMax("[TheSequence]", "YourTableName", "[TheYear] = "&Me.TheYear)

will return a null value because there will be no existing record with the new year. Nz turns this to a zero and then you add 1.

 

[jepoysaipan]

Thanks Neil, figured out that I have the wrong reference. my bad.