Adding an incrementing number to the middle of 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. Here is a sample of my code:

Private Sub Form_Current()


Dim intYearDiff As Integer
intYearDiff = Right(DMax("[invoice_No]", "invoice&quot, 2) - Format(Date, "yy&quot
'creates the difference between the years so that each year,index begins at yyyy0001'

If Me.NewRecord Then


Select Case intYearDiff

Case 0
Me.[INVOICE_NO] = "LB-" & Format(Date, "yy&quot Right(Left(DMax("[invoice_no]", "invoice&quot, 7), 4) + 1 & Format(Date, "yy&quot

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

End Select
End If

End Sub

Any help on this qould be greatly appreciated.

 

[JeremyNYC]

Stranger,

I don't know how much control you have over this database, but the big problem here is that you're storing too much information in one field. The Invoice number should be broken into three fields. You can always put them back together for viewing in forms, reports and queries. But look at all the hoops through which you have to jump to get your job done.

Also, look into the mid function, so you don't have to do "Right(left(". That will be a lot easier on your eyes, and easier to get right.

Also, you don't tell us what the format of your invoice number is, so it's hard to tell exactly where your code needs tweaking.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.


Remember to reward helpful tips with the stars they deserve.

 

[stanger1]

Jeremy,

Thanks for the help!