Alphabet autoincrement

[KWHAT]

I am trying to assign a unique two letter code to a set of record. From AA..AB..BA....all the way to ...ZZ, how do i go about doing this ??? IN VBA ?? Thank you for the help in advance!

[Robeen]

Upper case Alphabets have ASCII codes from 65 to 90 [in order from A - Z].
You could use those numbers to write VBA Code to increment the ASCII code.
For example:

Dim intFirst_Letter as Integer
Dim strCode As String, strFirst_Letter As String, strSecond_Letter As String

'Get the value of the Code into strCode here . . . I tested it with this:
strCode = Me.Text_Box_On_Form

'This gives you the first letter of the code - and converts it to an ascii number.
strFirst_Letter = Left(strCode, 1)
intFirst_Letter = Asc(strFirst_Letter)

'This will increment the ascii number and then convert it back to the 'incremented' letter.
intFirst_Letter = intFirst_Letter + 1
strFirst_Letter = Chr(intFirst_Letter)
MsgBox "New First Letter = " & strFirst_Letter & "."

You can use that as a starting point.

Hope this helps!

[orange]

Why? Is this just an exercise or is there some purpose?
You might want to Google "Cartesian Product" to assist with all 2 letter combinations.

[R_Badger]

Stupid question first, why!

Reason being this is quite hard from where my head is sitting, its doable, but likely to be long an complex, here's why!

Working off a similar basis to hexadecimal, you could work A=0 to Z=25.

you would then need to count the number of existing records, and add one to get the new record number.

Then you need to calculate the number to your first letter, this is done by fix([new_record_number]/25)

then calculate the number for your second letter, this is done by mod([new_record_number]/25)

your should then write a function (probably including a long select case) to translate the number to a letter.

My head isn't quite with it enough yet to write the full vb required, but the concept (i think) is there, assuming i understood your correctly.

[R_Badger]

Robeen, you know what, I never even thought of touching ASCII, I havent looked at it since I was using a BBC model B!

[KWHAT]

Im doing this for a project and it’s a requirement, and im sorry you find this a stupid question, I understand the work is lengthy but if it wasn’t doable I don’t think I would have been asked to create it, just needed some guidance thank you for the help.

[orange]

Funny how we see this differently.
I'd write a small sub to populate a table, with A thru Z 26 records
I would make a cartesian product using 2 copies of the table - to get all 2 char unique combinations.
Stuff these in a table with an id

Assign values to the new records based on the Id

[orange]

KWHAT,

it’s a requirement

What exactly will this 2alpha char unique field be used for? Why couldn't it be a unique number? I don't think anyone said it was a stupid question , but it is the kind of question that requires a little explanation. In my view it is a HOW you might do something, but I'd be asking WHAT is it you're trying to accomplish --there may be options.

[KWHAT]

Well there are limitations, I need to set the first two characters as a state and the last two need to be characters as well, so there a multiple groups that need to be assigned a unique ID within the same state therefore I need the last two letters to change incrementally, there can’t be any numerical values in the string .

[KWHAT]

@Orange thats a great idea with the two tables. Thanks!

[KWHAT]

But how do i go about after creating the two tables to get a cartesian product to of all 2 char unique combinations.

[R_Badger]

KWHAT I wasn't saying your question was stupid, its mine. Why doesnt help what, but as orange said understanding of concept sometimes reveals other options

[KWHAT]

KWHAT I wasn't saying your question was stupid, its mine. Why doesnt help what, but as orange said understanding of concept sometimes reveals other options

I totally understand i should have added more detail and explaination to what i was asking.

[orange]

I still don't accept that this is really a requirement that wouldn't/couldn't/shouldn't be solved in a different way, BUT

Here are a few steps for the purpose of the exercise.

Create a table, in my case TblAlphaChar, with 1 field SingleAlph text 1

Populate the table with a small procedure

Sub PopulateTbl()
Dim x As Integer
Dim s1 As Integer
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("tblAlphaChar")
s1 = 65 'capital A
For x = 0 To 25
rs.AddNew
rs!singlealph = Chr(s1 + x)
rs.Update
Next x
 rs.Close
End Sub

Run the following query (using 2 copies of tblAlphaChar a and b) to make a new table TblAlphaPairs with the unique combos if characters

SELECT A.singlealph & b.singlealph AS MyPair INTO tblAlphaPairs
FROM TblAlphaChar AS a, TblAlphaChar AS b;

Open tblAlphaPairs in design mode, add an Id field autonumber
save the table.
See then attached jpg for the values in tblAlphaPairs (and other pieces)

[KWHAT]

Thanks, for the steps they really helped, but for my own curiosity how would you go about creating a unique string with four characters, were the two preceding characters must be the state or province.