Auto increment letters? approach question 5

[LikeThisName]

I want to auto increment letters, so that when a user makes a new entry the "next letter" is assigned. It would never go past Z. There are of course a number of ways I can do this, but I have a feeling there is a better one than any of mine.

I was thinking I could make a table that lists A-Z and i could write a function to grab the next row/letter.

I was wondering if there was a way to turn a letter into a number, like ascii, and then turn it back into a letter, so i would actually just be incrementing a letter by one and checking to see if it had reached Z.

Any thoughts? guidance?

as always, your insight is greatly appreciated. thankyou

 

[jdegeorge]

[tt]I do something similar with an incremental number. After I know my record is going to be saved, I have this code in the afterinsert property:

If Format(Me![ctlTempID#], "000&quot = Right(Me![ctlIncident#], 3) Then
'Add 1 to the TempID#
Dim db As DAO.Database, rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("tblIncident#", dbOpenDynaset)

rs.Edit
rs![Incident#] = rs![Incident#] + 1
rs.Update
End If

I also have code to make sure that this temporary nubmer isn't incremented in the temp table until the record is committed. It's painful, but it works.[/tt]

Jim DeGeorge

 

[Savil]

Try calling this function

Public Function AutoIncrement(LetterIn As String) As String
Dim NumLetterIn As Integer

NumLetterIn = Asc(LetterIn)

'If statement prevents returning asci code greater than Z
If NumLetterIn = 90 Then
AutoIncrement = "Z"
Else
AutoIncrement = Chr(NumLetterIn + 1)
End If

 

[MichaelRed]

Public Function basAutoIncr(LtrIn As String) As String

    If (LtrIn < 90) Then
        basAutoIncr = Chr(Asc(LtrIn) + 1)
     Else
        basAutoIncr = &quot;Z&quot;
    End If

End Function

Perhaps a declaration and instruction or so less?

(Orwelllian?)




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[LikeThisName]

thank you all so much

 

[LikeThisName]

that's wierd.

Am i limited to giving only a 2 stars in one thread?

I tried to give each of you a star but its not letting me give Jim one.

 

[LikeThisName]

got it, there we go

 

[jdegeorge]

Thanks for the star. MichaelRed's and Savil's solutions were more flexible, but you can't look a gift star in the mouth!

Jim DeGeorge

 

[LikeThisName]

Michael only made one change.


Public Function basAutoIncr(LtrIn As String) As String

If (Asc(LtrIn) < 90) Then
basAutoIncr = Chr(Asc(LtrIn) + 1)
Else
basAutoIncr = &quot;Z&quot;
End If

End Function

 

[MichaelRed]

OOPs - meant to be:

[tab]If(ltrIn < &quot;Z&quot then

it is not functionally different, just easier to &quot;read&quot;, especially for the younger generation.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[kkson]

Looks good, now if I wanted to be able to go double or triple letters (aa,abc). Is that possible?

Thanks for any help.

kevin

 

[LikeThisName]

sure is

how many letters in the alphabet? 26?
so if the increment is more than 26 over what would represent A than it would be A then the letter or the letter printed for the number of times it is 26 over.

does that make sense / help you?

LikeThisName <- ?

 

[jdegeorge]

Sounds logical, but using MichaelRed's code how do you get past "Z" with the increment function?

If(ltrIn < "Z") Then
    basAutoIncr = Chr(Asc(LtrIn) + 1)
Else
    basAutoIncr = "Z"
End If

Jim DeGeorge

 

[kkson]

Thanks likethisname.

 

[MichaelRed]

Somewhere in the untrackable 'archives' of these fora there are other thread(s) with variation(s) on this them. Try Searching for [Incr & Str & Auto &| AlphaNum]. I "cogito ergo" it may actually be "basIncrAlph" with some other part to the procedure name.




MichaelRed