Problems Incrementing by 1 in VBA

[Matt_Burton]

I am trying to increment a SerialNumber field by 1 to show the user what the next SerialNumber code should be.
The problem I have is SerialNumber is a combination of 3 Characters and 4 Numbers. ie CAM0001, CAM0002 etc...

The problem I have hit is that when I use + 1 to the number a single digit is being returned.

Hence if CAM0001 is the SerialNumber the incremented value it gives me is CAM2, not CAM0002 as required.

The Code I am currently using to create the Increment is as follows:

Private Sub SerialNumber2_Enter()

Dim Tempa
Dim Tempb
Dim Tempc
Dim Tempd

Tempa = Left(SerialNumber, 3)
Tempb = Mid(SerialNumber, 4, 4)
Tempc = Tempb + 1
Tempd = Tempa & Tempc

Me!Text2 = Tempd

End Sub

Text2 is my output textbox which I hoped would display CAM0002 but instead displays CAM2!

Does anyone have any ideas how to create a number increase and ensure a single digit isn't returned?

 

[Everton]

I don't know if this is the best way, but I imagine it'll work:

Tempa = Left(SerialNumber, 3)
Tempb = Mid(SerialNumber, 4, 4)
Tempc = Tempb + 1

Select Case Tempc
Case Is < 10
Tempc = "000" & Tempc
Case Is < 100
Tempc = "00" & Tempc
Case Is < 1000
Tempc = "0" & Tempc
End Select

Tempd = Tempa & Tempc

HTH,
Paul.

 

[Atomic Shrimp]

Storing a mixture of data (if that's what you are doing) in a single field isn't a good idea, so maybe you want to store CAM and 0001 in separate fields, then concatenate them in a query whenever you want to use them.

If you absolutely have no choice, then this expression ought to work:

Me!Text2 = Left([SerialNumber],3) & Format(Val(Right([SerialNumber],4)+1),"0000")

HTH

Mike

 

[jimbrooking]

Try this:

Tempa = Left(SerialNumber, 3)
Tempb = Mid(SerialNumber, 4, 4)
Tempc = Format(Tempb + 1,"0000")

Tempd = Tempa & Tempc


[This message has been edited by jimbrooking (edited 08-01-2001).]

[This message has been edited by jimbrooking (edited 08-01-2001).]

[This message has been edited by jimbrooking (edited 08-01-2001).]

 

[Matt_Burton]

Thanks for the responses, it's working fine now.