Formatting number

[kcorrigan]

I have a value being sent to a label field. Example of value is 'A000000001'. When I hit a button, I need to insert the next interval, which would be 'A000000002', into a table. The problem I'm having is getting the next number to produce. If I trim the 'A' off the front and add 1 to the 000000001, the result is '2' instead of '000000002'. Any suggestions? I don't want to have to do a long formula to add so many zeroes depending on the number of digits, I'm hoping there's something simpler. Maybe a VB version of lpad?

 

[kcorrigan]

Found my answer. My label is 20 characters, and this is how I formatted the next number:

lblAuditor2 = Mid(lblAuditor2, 1, 1) & _
Right(Left$(String$(18, "0&quot & Mid(lblAuditor2, 2, 19) + 1, 19), 19)

 

[Imhoteb]

Hi,

Could you try this please
Maybe it is not the fasted way but it works
Put two textboxes and a commandbutton on a form.
In textbox1(Text1) you put your number'A000000001'
Textbox 2 (Text2) will give you the result even when the numberhas more than 1 digit.
You will also see that, when you reached 9 as last digit and you add 1 to 9 you get 10(2 ditgit number), the length of the original number and the new number will be the same.

********************************************************

Private Sub Command1_Click()
Dim myLen1 As Byte
Dim myLen2 As Byte
Dim myCounter As Byte
Dim myString1 As String
Dim myString2 As String
myLen1 = Len(Text1)
myCounter = 1
For myCounter = 1 To myLen1
    myString1 = Mid(txtNummer, myCounter , 1)
    If IsNumeric(myString1) Then
        myString1 = Mid(txtNummer, myCounter , (myLen1 + 1) - myCounter )
        myString1 = myString1 + 1
        myLen2 = Len(myString1)
        Exit For
    End If
Next myCounter 
myString2 = Left(Text1, myLen1 - myLen2)

Text2= myString2 & myString1
End Sub

*****************************************************

Hope this helps

Good luck

 

[kcorrigan]

You are right, when I get to A0000000000000000009, it does not go to A0000000000000000010 it goes back to 1. But this code you gave me does not change to the next available number, it stays the same.

 

[Swi]

Dim Test As Variant
Test = "A000000001"
If Not IsNumeric(Mid$(Test, 1, 1)) Then
MsgBox Mid$(Test, 1, 1) & Format$(Val(Mid$(Test, 2)) + 1, _
String$(Len(Test) - 1, "0&quot)
Else
MsgBox Format$(Val(Test) + 1, String$(Len(Test), "0&quot)
End If Swi

 

[kcorrigan]

PERFECT Swi! Thanks so much!!!

~KC

 

[DrJavaJoe]

Dim intValue As Integer
intValue = Mid(label1.caption, 2)
label1.caption = Format(intValue + 1 , "A00000000#&quot

 

[kcorrigan]

Swi,
Now I'm noticing that A0001000000000000000 is the highest number it will accept. Is this a VB issue?

 

[Swi]

The number is too large. VB has its limits. Swi

 

[kcorrigan]

Understandable! Thanks for your help!!!

 

[DrJavaJoe]

Try using double
Dim dblValue As Double
dblValue = Mid(label1.caption, 2)
label1.caption = Format(dblValue + 1 , "A000000000000000000#&quot

 

[strongm]

Actually, the number is not too big. The problem is one of accuracy. The numeric type that VB is using in the background for the above calculation is a single, made up of sign, mantissa, and exponent. This is all squeezed into 4 bytes, and therefore there is a limit on the number of meaningful digits that can be held in the mantissa. The least significant ones get lost.

 

[Imhoteb]

Hi,

Indeed in my code there is an error
txtNumber should be Text1
It has to be changed twice