Allen Browne

N

nicole62282

Allen,

I read a post of yours back in 2006 about incremental numbers and modified
the code that you posted to fit my needs. My incremental number starts over
each September and your code worked beautifully (you are a genius). The
following is the modified code that I used:

Private Sub payroll_dt_AfterUpdate()
Dim varMax As Variant
payroll_no = Right(Year(DateAdd("m", 4, [payroll_dt])), 2) & "-"
varMax = DMax("payroll_no", "tbl_payroll_no", "payroll_no Like """ &
payroll_no & "*""")
varMax = Val(Nz(Mid(varMax, 5), 0)) + 1
payroll_no = payroll_no & Format(varMax, "000")
End Sub

Let's say I used todays date and my last number used was 65...then the next
number looks like this...09-66. ("09" is the last 2 digits of the Current
Fiscal Year)

As I am still a novice to Access, I tested the code out and when it hits
09-100 it stops and goes back to 09-01.

Our numbers generally only go up to about 300 or so. Is there a different
piece of code I need to add or change to the above to let the number go past
100?

Thanks in advance.
 
S

Steve Sanford

Looking at the example you gave, if the max payroll_no is 09-100,then
the Max() function from this line

varMax = Val(Nz(Mid(varMax, 5), 0)) + 1

would return "00", not "100" because the first zero is the 5th char from the
start of the string.

You should start at the 4th char (ie the first char after the "-").


Try replacing the "5" in this line

varMax = Val(Nz(Mid(varMax, 5), 0)) + 1

with "4".

The line should look like:

varMax = Val(Nz(Mid(varMax, 4), 0)) + 1


I think this should allow a number sequence up to "999" , then start over at
zero.


HTH
 
N

nicole62282

Thanks for the reply Steve. I will give that a try and let you know my
progress.
Steve Sanford said:
Looking at the example you gave, if the max payroll_no is 09-100,then
the Max() function from this line

varMax = Val(Nz(Mid(varMax, 5), 0)) + 1

would return "00", not "100" because the first zero is the 5th char from the
start of the string.

You should start at the 4th char (ie the first char after the "-").


Try replacing the "5" in this line

varMax = Val(Nz(Mid(varMax, 5), 0)) + 1

with "4".

The line should look like:

varMax = Val(Nz(Mid(varMax, 4), 0)) + 1


I think this should allow a number sequence up to "999" , then start over at
zero.


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


nicole62282 said:
Allen,

I read a post of yours back in 2006 about incremental numbers and modified
the code that you posted to fit my needs. My incremental number starts over
each September and your code worked beautifully (you are a genius). The
following is the modified code that I used:

Private Sub payroll_dt_AfterUpdate()
Dim varMax As Variant
payroll_no = Right(Year(DateAdd("m", 4, [payroll_dt])), 2) & "-"
varMax = DMax("payroll_no", "tbl_payroll_no", "payroll_no Like """ &
payroll_no & "*""")
varMax = Val(Nz(Mid(varMax, 5), 0)) + 1
payroll_no = payroll_no & Format(varMax, "000")
End Sub

Let's say I used todays date and my last number used was 65...then the next
number looks like this...09-66. ("09" is the last 2 digits of the Current
Fiscal Year)

As I am still a novice to Access, I tested the code out and when it hits
09-100 it stops and goes back to 09-01.

Our numbers generally only go up to about 300 or so. Is there a different
piece of code I need to add or change to the above to let the number go past
100?

Thanks in advance.
 
N

nicole62282

Steve,

Thanks so much. It was exactly what I needed.

nicole62282 said:
Thanks for the reply Steve. I will give that a try and let you know my
progress.
Steve Sanford said:
Looking at the example you gave, if the max payroll_no is 09-100,then
the Max() function from this line

varMax = Val(Nz(Mid(varMax, 5), 0)) + 1

would return "00", not "100" because the first zero is the 5th char from the
start of the string.

You should start at the 4th char (ie the first char after the "-").


Try replacing the "5" in this line

varMax = Val(Nz(Mid(varMax, 5), 0)) + 1

with "4".

The line should look like:

varMax = Val(Nz(Mid(varMax, 4), 0)) + 1


I think this should allow a number sequence up to "999" , then start over at
zero.


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


nicole62282 said:
Allen,

I read a post of yours back in 2006 about incremental numbers and modified
the code that you posted to fit my needs. My incremental number starts over
each September and your code worked beautifully (you are a genius). The
following is the modified code that I used:

Private Sub payroll_dt_AfterUpdate()
Dim varMax As Variant
payroll_no = Right(Year(DateAdd("m", 4, [payroll_dt])), 2) & "-"
varMax = DMax("payroll_no", "tbl_payroll_no", "payroll_no Like """ &
payroll_no & "*""")
varMax = Val(Nz(Mid(varMax, 5), 0)) + 1
payroll_no = payroll_no & Format(varMax, "000")
End Sub

Let's say I used todays date and my last number used was 65...then the next
number looks like this...09-66. ("09" is the last 2 digits of the Current
Fiscal Year)

As I am still a novice to Access, I tested the code out and when it hits
09-100 it stops and goes back to 09-01.

Our numbers generally only go up to about 300 or so. Is there a different
piece of code I need to add or change to the above to let the number go past
100?

Thanks in advance.
 
S

Steve Sanford

You're welcome.


--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


nicole62282 said:
Steve,

Thanks so much. It was exactly what I needed.

nicole62282 said:
Thanks for the reply Steve. I will give that a try and let you know my
progress.
Steve Sanford said:
Looking at the example you gave, if the max payroll_no is 09-100,then
the Max() function from this line

varMax = Val(Nz(Mid(varMax, 5), 0)) + 1

would return "00", not "100" because the first zero is the 5th char from the
start of the string.

You should start at the 4th char (ie the first char after the "-").


Try replacing the "5" in this line

varMax = Val(Nz(Mid(varMax, 5), 0)) + 1

with "4".

The line should look like:

varMax = Val(Nz(Mid(varMax, 4), 0)) + 1


I think this should allow a number sequence up to "999" , then start over at
zero.


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


:

Allen,

I read a post of yours back in 2006 about incremental numbers and modified
the code that you posted to fit my needs. My incremental number starts over
each September and your code worked beautifully (you are a genius). The
following is the modified code that I used:

Private Sub payroll_dt_AfterUpdate()
Dim varMax As Variant
payroll_no = Right(Year(DateAdd("m", 4, [payroll_dt])), 2) & "-"
varMax = DMax("payroll_no", "tbl_payroll_no", "payroll_no Like """ &
payroll_no & "*""")
varMax = Val(Nz(Mid(varMax, 5), 0)) + 1
payroll_no = payroll_no & Format(varMax, "000")
End Sub

Let's say I used todays date and my last number used was 65...then the next
number looks like this...09-66. ("09" is the last 2 digits of the Current
Fiscal Year)

As I am still a novice to Access, I tested the code out and when it hits
09-100 it stops and goes back to 09-01.

Our numbers generally only go up to about 300 or so. Is there a different
piece of code I need to add or change to the above to let the number go past
100?

Thanks in advance.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top