• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 70
  • Last Modified:

Auto Increment Text Field


I have a 2 character text field that I need to increment automatically in a table via VBA.  For example, I have a record with the pseudo key AA that I need to create a new record for under pseudo key AB.  I have thousands of records that I need to do this for.  Here are some examples of the keys I'm working with: AA, CB, AZ, ZA, PQ, et...the records that are created look at the alphabet for the second digit and adds the next consecutive letter.   How would I be able to do this via VBA?

Thank you.
1 Solution
Rey Obrero (Capricorn1)Commented:
you need to use VBA codes to achieve this

test this function,

Function getNextValue(vStr As String)
Dim vChar As Long, firstChar As String
firstChar = Left(vStr, 1)
vChar = Asc(Right(vStr, 1))

getNextValue = firstChar & Chr(vChar + 1)
End Function

Open in new window

this one goes a bit further: (increments to ZZ)

Function getNextValue(vStr As String) As String
  Dim vChar As Long, firstChar As String, secondChar As String
  firstChar = Left(vStr, 1)
  secondChar = IIf(Len(vStr) = 2, Right(vStr, 1), "")
  If Asc(secondChar) = 90 And Asc(firstChar) = 90 Then MsgBox "We have a ZZ problem!", vbCritical
  If Asc(secondChar) < 90 Then
    secondChar = Chr(Asc(Right(vStr, 1)) + 1)
    firstChar = Chr(Asc(firstChar) + 1)
    secondChar = Chr(65)
  End If
  getNextValue = firstChar & secondChar
End Function
axfernandAuthor Commented:
Hi Guys,

Haven't had a chance to apply your code but will do next week.  Thanks for the responses.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now