CODEWITHSUNDEEP
ms-accessvba
Jaro
Jaro

Reputation: 43

VBA Type mismatch when trying to increment text number

I'm trying to set up a code in MS Access that increments the last four positions of a text field. The numbers in the text field have seven digits. For example:

0010012

0010013

First three digits represent the manuacturer and the last four the product. These are the ones I want to increment. I am using the code below, which I found online, and it is supposed to be working but I keep getting the error: "Run-time error '13': Type mismatch"

Dim varSifra As Variant
varSifra = DMax("[Sifra]", "tblProducts", "[Manufacturer] = " & Forms!frmProduct!Manufacturer)
Me.[Sifra] = Left(varSifra, 3) & Format(Val(Right(varSifra, 4)) + 1, "0000")

I tried the code without the Format function but instead of incremented number 0010014 I get 00114

Upvotes: 0

Views: 704

Answers (3)

Chris Rolliston
Chris Rolliston

Reputation: 4808

You can use a simple Format call fine, however the input needs to be explicitly converted to a Long first:

Function IncProductNumber(Value)
    If IsNull(Value) Then
        Let IncProductNumber = Null
    Else
        Let IncProductNumber = Format(CLng(Value) + 1, "0000000")
    End If
End Function

Or, more generically, the desired padding could be inferred from the input:

Function IncTextNumber(Value)
    If IsNull(Value) Then
        Let IncTextNumber = Null
    Else
        Let IncTextNumber = Format(CLng(Value) + 1, String$(Len(Value), "0"))
    End If
End Function

IncTextNumber("0123") will produce "0124", IncTextNumber("00999") will produce "01000" and so on.

Upvotes: 2

Linger
Linger

Reputation: 15068

Dim tempManProd As String, tempNumToInc As Integer

tempManProd = 'get the value you are wanting to increment
tempNumToInc = CInt(right(tempManProd, 4))
tempNumToInc = tempNumToInc + 1

'This will make sure that the 0s get added back to the front of the product
Do While (Len(tempManProd & "") + Len(tempNumToInc & "")) < 7
  tempManProd = tempManProd & "0"
Loop

tempManProd = tempManProd & CStr(tempNumToInc)

Upvotes: 1

Alex P
Alex P

Reputation: 12497

Can this help?

Sub Test()
    Debug.Print IncrementProduct("0010001") //Prints 0010002
    Debug.Print IncrementProduct("0010012") //Prints 0010013
    Debug.Print IncrementProduct("0010099") //Prints 0010100
End Sub


Function IncrementProduct(code As String) As String
    Dim manufacturerCode As String, padding As String, productCode As String

    manufacturerCode = VBA.Left$(code, 3)
    productCode = CInt(VBA.Right$(code, Len(code) - Len(manufacturerCode))) + 1
    padding = Application.WorksheetFunction.Rept("0", 4 - Len(productCode))

    IncrementProduct = manufacturerCode & padding & productCode
End Function

Upvotes: 2

Related Questions