What's wrong with this auto numbering code?

  • Thread starter Thread starter AJ
  • Start date Start date
A

AJ

I currently have code that's been working fine all year. It assigns a
new RMA number to a case based on the year, the month and the
sequential order of RMA numbers already in the database,

For in example, 05L120, "05" represents the year, "L" represents the
month as a letter, and "120" is the next RMA for that month. At each
month the number resets to zero and it goes to the next letter.

My problem is that it did not go to the new year "06A001" today. For
some reason it's still putting 05 at the begining of the number. Now
if I get rid of the "yy" part of the code, it calculates the current
year correctly, but it puts "2006A001" and I only want the two-digit
year.

Any ideas? Here is the code:

Dim strFind As String
Dim varLastRMA As Variant
Dim strNextRMA As String


strFind = Format(Year(Date), "yy") & _
Choose(Month(Date), "A", "B", "C", "D", "E", "F", "G", "H",
"I", "J", "K", "L")
varLastRMA = DMax("[RMA NUMBER]", "RMA INFO", _
"Left([RMA NUMBER], 3) = '" & strFind & "'")
If IsNull(varLastRMA) Then
strNextRMA = strFind & "001"
Else
strNextRMA = strFind & Format(CLng(Right(varLastRMA, 3)) + 1,
"000")
End If

Forms![RMA Entry]![RMA Number] = strNextRMA
 
AJ said:
I currently have code that's been working fine all year. It assigns a
new RMA number to a case based on the year, the month and the
sequential order of RMA numbers already in the database,

For in example, 05L120, "05" represents the year, "L" represents the
month as a letter, and "120" is the next RMA for that month. At each
month the number resets to zero and it goes to the next letter.

My problem is that it did not go to the new year "06A001" today. For
some reason it's still putting 05 at the begining of the number. Now
if I get rid of the "yy" part of the code, it calculates the current
year correctly, but it puts "2006A001" and I only want the two-digit
year.

Any ideas? Here is the code:

Dim strFind As String
Dim varLastRMA As Variant
Dim strNextRMA As String


strFind = Format(Year(Date), "yy") & _
Choose(Month(Date), "A", "B", "C", "D", "E", "F", "G", "H",
"I", "J", "K", "L")
varLastRMA = DMax("[RMA NUMBER]", "RMA INFO", _
"Left([RMA NUMBER], 3) = '" & strFind & "'")
If IsNull(varLastRMA) Then
strNextRMA = strFind & "001"
Else
strNextRMA = strFind & Format(CLng(Right(varLastRMA, 3)) + 1,
"000")
End If

Forms![RMA Entry]![RMA Number] = strNextRMA
Click to expand...

You don't want to format the current year as "yy". You want to format the
current date as "yy". When formatting as "yy" the Format() function expects a
date as input. Year(Date) returns an integer, not a date.

Try using strFind = Format(Date, "yy") & _
etc..
 
I currently have code that's been working fine all year. It assigns a
new RMA number to a case based on the year, the month and the
sequential order of RMA numbers already in the database,

For in example, 05L120, "05" represents the year, "L" represents the
month as a letter, and "120" is the next RMA for that month. At each
month the number resets to zero and it goes to the next letter.

My problem is that it did not go to the new year "06A001" today. For
some reason it's still putting 05 at the begining of the number. Now
if I get rid of the "yy" part of the code, it calculates the current
year correctly, but it puts "2006A001" and I only want the two-digit
year.

Any ideas? Here is the code:

Dim strFind As String
Dim varLastRMA As Variant
Dim strNextRMA As String


strFind = Format(Year(Date), "yy") & _
Choose(Month(Date), "A", "B", "C", "D", "E", "F", "G", "H",
"I", "J", "K", "L")
varLastRMA = DMax("[RMA NUMBER]", "RMA INFO", _
"Left([RMA NUMBER], 3) = '" & strFind & "'")
If IsNull(varLastRMA) Then
strNextRMA = strFind & "001"
Else
strNextRMA = strFind & Format(CLng(Right(varLastRMA, 3)) + 1,
"000")
End If

Forms![RMA Entry]![RMA Number] = strNextRMA
Click to expand...

Use Format(Date, "yy")

P
 
Back
Top