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: 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 = DMax([Temp_inv#])(Left([Temp_inv#], 2) - Format(Date, "yy"))


If Me.NewRecord Then
Select Case intYearDiff

Case 0
DMax (Temp_Inv#) + 1


Case 1
30001 [Temp_inv#] [INVOICE_NO] = "LB" & Right(Temp_Inv#, 4) & "-" & Format(Date, "yy")

End Select
End If


End Sub

Any help on this qould be greatly appreciated.

 

[AlanS]

If CurNum is your current highest number, and MyDate is the date whose year you want to use, you can construct the string you want with the following code:

Function NewInvNum (CurNum As Long, MyDate As Date)
Dim lYear As Long, lNum As Long
Dim sYear As String, sNum As String
lYear = DatePart("yyyy", MyDate)
sYear = Right$(Format$(lYear), 2)
lNum = CurNum + 1
sNum = Right$("000" & Format$(lNum), 4)
NewInvNum = "LB-" & sNum & "-" & sYear
End Function

 

[stanger1]

String

AlanS,
Thank you for your reply. I am having problems with the coding... I am new at this and need just a little more guidance as to where the code should be placed.

 

[AlanS]

Put the function code into any standard module (click the Modules tab, click New, enter the code, save and close the module).

If you want the string to be constructed and assigned to the field MyText1 on form MyForm (which MUST be open in Form view at the time), you would need just this line:

Forms!MyForm!MyText1 = NewInvNum(CurNum, MyDate)

If you are doing this from MyForm's own code module, you could shorten it to:

Me!MyText1 = NewInvNum(CurNum, MyDate)

Exactly where you put this statement depends on when you want the assignment to happen. If you want it to occur just before a new or modified record is saved, you would put the statement in the form's BeforeUpdate event procedure. If you want it to happen when the user clicks a command button, you would put it in the command button's Click event procedure.

 

[stanger1]

alanS,

Thanks for the help. It worked!