I have a field that needs to auto increment each time a new entry is
made. It is a text field that needs to increment in a similar way to
Excel columns ie:

A, B, C...

continuing like this

...X, Y, Z, AA, AB, AC. etc...

The maximum it needs to go to is ZZZ

How do I do this in VBA? Convert each letter combination to a number,
add one and then convert it back to text? Or is there a better way
(built in function)?

Thanks Paul

On Fri, 03 Jul 2009 02:52:02 -0700, Paul H wrote:

Build and pre-populate a table like this...

NumField AlphaField
1 A
2 B
etc...

In your table do a traditional DMax() + 1 with a numeric field. For
presentation you can join to the pre-populated table above to pull in the
desired alpha code.


--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Paul H wrote:
Public Function GetNumberFromABC(arg As String) As Long

Dim a() As Byte
Dim i As Long, k As Long, x As Long

If arg Like "*![A-Z]*" Then
GetNumberFromABC = 0
Else

a = StrConv(UCase(arg), vbFromUnicode)
k = UBound(a)
For i = k To LBound(a) Step -1
x = x + (a(i) - 64) * 26 ^ (k - i)
Next
GetNumberFromABC = x
End If
End Function

--
KN

Krzysztof Naworyta wrote:


Public Function GetNumberFromABC(arg As String) As Long

Dim a() As Byte
Dim i As Long, k As Long, x As Long

If arg Like "*[!ABCDEFGHIJKLMNOPQRSTUVWXYZ]*" Then
GetNumberFromABC = 0
Else

a = StrConv(UCase(arg), vbFromUnicode)
k = UBound(a)
For i = k To LBound(a) Step -1
x = x + (a(i) - 64) * 26 ^ (k - i)
Next
GetNumberFromABC = x
End If
End Function

--
KN

Krzysztof Naworyta wrote:

Public Function GetNumberFromABC(arg As String) As Long

Dim a() As Byte
Dim i As Long, k As Long, x As Long

If arg Like "*[!ABCDEFGHIJKLMNOPQRSTUVWXYZ]*" Then
GetNumberFromABC = 0
Else

a = StrConv(UCase(arg), vbFromUnicode)
k = UBound(a)
For i = k To LBound(a) Step -1
x = x + (a(i) - 64) * 26 ^ (k - i)
Next
GetNumberFromABC = x
End If
End Function


--
KN

There are a few questions you need to address in this but the following
function seems to get close to what you want.

'*********** Code Start *************
Function IncrAlpha(StartValue) As String
Dim strStartValue As String
Dim strVals(1 To 3) As String
Dim strTemp As String
Dim intX As Integer
Dim bInc As Boolean
Dim strPlaceMarker As String

Const MIN_VAL_PLACE = "A"
Const MAX_VAL_PLACE = "Z"
Const MAX_VAL = "ZZZ"

strPlaceMarker = Chr(Asc(MIN_VAL_PLACE) - 1)

strStartValue = Right(String(3, strPlaceMarker) & Trim(StartValue & ""),
3)

If strStartValue < MAX_VAL Then
For intX = 3 To 1 Step -1
strTemp = Trim(Mid(strStartValue, intX, 1))
If Asc(strTemp) < Asc(MIN_VAL_PLACE) Then
strTemp = strPlaceMarker
ElseIf Asc(strTemp) > Asc(MAX_VAL_PLACE) Then
strTemp = MAX_VAL_PLACE
End If
strVals(intX) = strTemp
Next

bInc = False
For intX = 3 To 1 Step -1
strTemp = strVals(intX)

If strTemp >= MAX_VAL_PLACE Then
strVals(intX) = MIN_VAL_PLACE
bInc = True
Else
strVals(intX) = Chr(Asc(strTemp) + 1)
bInc = False
End If
If Not bInc Then
Exit For
End If
Next

For intX = 3 To 1 Step -1
If strVals(intX) < MIN_VAL_PLACE Then
strVals(intX) = vbNullString
End If
Next
strTemp = Join(strVals, vbNullString)
Else
' Need to decide what to return if input is "ZZZ"
strTemp = ""
End If

IncrAlpha = strTemp

End Function
'************ Code End *************


--
Terry Kreft


"Paul H" <comcraft1966 (AT) googlemail (DOT) com> wrote

Paul H <comcraft1966 (AT) googlemail (DOT) com> wrote in
news:41c9604d-72cb-4b4f-8b9f-99a8a4d4e2d5 (AT) h8g2000yqm (DOT) googlegroups.com
:

Here's a function to increment revision levels on Documents.
Note that it skips over certain characters, if you don't need that
functionality, just add the missing letters to the stRevSeq variable
in the right position. It will return from 1 to 32767 characters..


Public Function bumpRev(ByVal RevLetter As String) As String
' Copyright R. Quintal, 2005
' Permission to use and modify is given, please give the author
' credit and pass along the modifications.

Dim stRevSeq As String 'revisions
Dim iRevDigit As Integer
Dim iRevValue As Integer
Dim bCarry As Boolean

' Define Allowable revisions in sequence
' IOQSXZ are disallowed by ASME Y14.35M-1997
' because they can be confused with numbers.
stRevSeq = "ABCDEFGHJKLMNPRTUVWY"
bCarry = True

RevLetter = " " & UCase(Trim(RevLetter))
iRevDigit = Len(RevLetter)

Do While bCarry = True
iRevValue = InStr(stRevSeq, Mid(RevLetter, iRevDigit, 1))

If RevLetter = " -" Then
RevLetter = "A"
bCarry = False
ElseIf RevLetter = " " Then
RevLetter = ""
bCarry = False
ElseIf Mid(RevLetter, iRevDigit, 1) = Chr(32) Then
Mid(RevLetter, iRevDigit, 1) = "A"
bCarry = False
ElseIf iRevValue = 0 Then
RevLetter = ""
bCarry = False
ElseIf iRevValue = Len(stRevSeq) Then
Mid(RevLetter, iRevDigit, 1) = "A"
iRevDigit = iRevDigit - 1
Else
Mid(RevLetter, iRevDigit, 1) = Mid(stRevSeq, iRevValue + 1,
1) bCarry = False
End If
Loop
bumpRev = Trim(RevLetter)
Exit_BumpRev:
Exit Function
End Function


--
Bob Quintal

PA is y I've altered my email address.

Thank you every one for your answers. I have a few approaches to try
out.

Cheers,

Paul

Paul H wrote:
What is your question. Do you not know how to do a Dmax()? Online help
will help.

Maybe it'd be more conducive for responses if you were a bit more
verbose, maybe provided an example of what you are doing currently.