Incrementing a number in a string

[stanger1]

I am trying to add an incrementing 4 digit number to the middle of a
string. The incrementing number is not working. There must be a better
way of writing this. The result I would like should look like this:

Invoice_no is the field in my test_no table. The value should look like this: LB-0001-02 Where LB is static, 0001 is the incrementing number and 02
is the year. Here is a sample of my code:
Private Sub Form_Current()


Dim intYearDiff As Integer
intYearDiff =format(Date,"yy&quot-Left(DMax("[Invoice_no]","testno&quot,4)


If Me.NewRecord Then
Select Case intYearDiff

Case 0
Me.[Invoice_no]=DMax ("[invoice_no]","testno&quot + 1


Case 1
Me.[Invoice_no]=Format(Date, "yy&quot&"0001"

End Select
End If


End Sub

Any help on this qould be greatly appreciated.

 

[MichaelRed]

Just use "Split" to seperate the "parts" of your value. Look up the function in help. Treat the seperate parts however necessary.

DO NOT use the result as a unique index if this is a multiuser db, as it WILL NOT guarntee uniqueness.

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[SBendBuckeye]

Public Function BuildString(pstrText) As String
Dim strYear As String
Dim strPrefix As String
Dim strSuffix As String
Dim strMiddle As String

strYear = Right$(Str(Year(Now())), 2)
strPrefix = Left$(pstrText, 3) 'LP-
strSuffix = Right$(pstrText, 3) '-02

If Right$(pstrText, 2) <> strYear Then
BuildString = strPrefix & &quot;0000-&quot; & strYear 'Or 0001-
Else
strMiddle = Format((Val(Mid$(pstrText,4,4))+1),&quot;0000&quot
BuildString = strPrefix & strMiddle & strSuffix
End If
End Function


Good Luck!
Please remember to give helpful posts the stars they deserve! This makes the post more visible to others in need!

 

[stanger1]

Thanks for your help!