Increment an alphanumeric string 1

[kawika1975]

Hi,

How do I go about incrementing an alphanumeric string by 1? For example: stCustomer is a string that contains a value of C012. I am trying to increment the string by +1 to get a value of C013. I know this sounds really simple, however I am having trouble finding a solution.

Thanks for your help

 

[MichaelRed]

subject has been discussed (in depth) her previously. Please use search / advanced ssearch.




MichaelRed

 

[kawika1975]

Sorry MichaelRed, I found your previous post, however I am having trouble understanding it. The person that you helped seems to have found a solution, however I am having trouble understanding his code. It seems he wrote a whole bunch of code to increment a alphanumeric string.

 

[MichaelRed]

" ... he wrote a whole bunch of code to increment a alphanumeric ..."

In your original post to this trhread, you mention

" am having trouble finding a solution ... "

, but did not include a requirement that the soloution be 'simple' or not involve code or ...

By "previous post", I assume that you are refering to thread222-97999. It includes a variety of soloutions, and some commentary by a number of members. If you check the stats on the members who provided the soloutions, I think you may see that at least a couple have been actively involved in VB(A) and have provided a number of soloutions to difficult issues. This thread is in the Ms. A. MODULES (VBA Coding) section, so it only seems reasonable to expect soloutions including code. I can only suggest that you at least TRY to use one of the 'canned' soloutions in the references thread.





MichaelRed

 

[TheAceMan1]

How are ya kawika1975 . . .

I gave this a shot trying to use the [blue]DMax[/blue] aggregate function, but other [blue]required embedded functions[/blue] really slowed it down (DMax is slow enough itself). So I was forced to us a recordset using DAO to maintain speed. The following is a function that will return the next increment of stCustomer:

[blue]Public Function NextST()
   Dim db As DAO.Database, rst As DAO.Recordset, SQL As String
   
   Set db = CurrentDb
   SQL = "SELECT Nz(Max(Val(Right([stCustomer],3)))) AS stMax " & _
         "FROM YourTableName;"
   Set rst = db.OpenRecordset(SQL, dbOpenDynaset)
   
   If rst.BOF Or rst!stMax = "" Then
      NextST = "C001"
   ElseIf rst!stMax < 10 Then
      NextST = "C00" & CStr(rst!stMax + 1)
   ElseIf rst!stMax < 100 Then
      NextST = "C0" & CStr(rst!stMax + 1)
   Else
      NextST = "C" & CStr(rst!stMax + 1)
   End If

End Function[/blue]

Use the function like you would any other!

[blue]   Me!ControlName = NextST()[/blue]

Note: The code requires [purple]Microsoft DAO 3.6 Object Library[/purple] to run. To [blue]check/install[/blue] the library, in any code window click [blue]Tools[/blue] - [blue]References...[/blue] In the listing find the library and [blue]make sure its checked.[/blue] Then using the up arrow, [purple]push it up as high in priority as it will go[/purple]. Click OK.

See Ya! . . . . . .

 

[kawika1975]

TheAceMan1,

Thank you for your response. I am going to give that one a try and I'll let you know how it turns out.

Thanks so much.

 

[kawika1975]

TheAceMan1,

Hey, it works great. Thanks again.