Allen Browne

  • Thread starter Thread starter nicole62282
  • Start date Start date
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.
 
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
 
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.
Click to expand...
Click to expand...
 
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.
Click to expand...
Click to expand...
Click to expand...
 
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.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top