Numbers did not reset Jan. 1

[Matt]

I am using access 2003 in XPsp2

I have a log that assigns consecutive numbers preceded by a 2 digit year.
(ex 10-001) On Jan. 1 the number was supossed to change and reset from
09-690 (last number of the year) to 10-001. This did not happen. The year
changed but the number carried over showing 10-691.

Here is the VB code that was used for this change I think. I did not write
it and my VB knowledge is poor.

Me.txtIncrNum.Enabled = True
Me.txtIncrNum.SetFocus
Me.txtIncrNum.Text = Nz(DMax("[IncrNum]", "[tblincidents]", _
"Year([datefield]) = " & Year(Date)), 0) + 1

Thanks for your help.

 

[Jeff Boyce]

Matt

I didn't spot any place in your code that tests to see if today's date is in
a new year before "adding one".

I'm thinking your code needs to do something like the following (untested
pseudocode):

* look at the largest existing value
* get the leftmost two characters
* get the rightmost two characters of the current date's year
* compare ... if they are different, you just crossed a year boundary,
so you need to start the sequence number over again at 1
* otherwise, just add one

Good luck!

--

Regards

Jeff Boyce
Microsoft Access MVP

Disclaimer: This author may have received products and services mentioned in
this post. Mention and/or description of a product or service herein does
not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[vanderghast]

In the Immediate Window (Ctrl_G), what returns

? Nz(DMax( "IncrNum", "tblIncidents", "Year(dateField)=" & 2010 ), 0)

if it returns 0, then check the computer date AND time, it may be offset by
12 hours (or something like that), and probably is if, TODAY,


? Nz(DMax( "IncrNum", "tblIncidents", "Year(dateField)=" &
Year(date()) ), 0)

returns 0.

It may also be some problem if you have a variable name Date, since then, in
VBA, the code:


Nz(DMax( "IncrNum", "tblIncidents", "Year(dateField)=" & Year(date) ),
0)


may use that variable (or form control), rather than the function date( )
(Name collision, ... bad idea to have a variable name the same as a
nilhadique function ). Try adding the optional ( ) after date to see if it
works? (and change the control/variable named 'date' to avoid further
problem).


Vanderghast, Access MVP