Increment letters VBA (1 Viewer)

Recons

New member
Local time
Today, 15:07
Joined
May 4, 2020
Messages
6
Hi all,

My name is Andris from the UK, I currently working as a production planner and trying to upgrade our current Goods Intake database to avoid typing errors.
At this moment before progress further, I'm stuck at increment letters in alphabetical order based on the last record added to the table.
In addition to complex things it even more we have and Julian date in the front (Example 125A;125B...).
I have created a custom form to add new records and I have found code for Julian date : Me.ColumnName = Format(DatePart("y", Date), "000") = returns as 3 digit number.
Also, letter increment would go 125a,125b,125c..125.z and goes on to 125aa, 125ab,125ac..and so on until next Day all resets due to new date 126a,126b....


Is there a way how to combine these two?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 08:07
Joined
Oct 29, 2018
Messages
21,359
Hi Andris. Welcome to AWF!

I would recommend to leave them separate. You don't even need the Julian date part, because you know how to calculate it from a date, so just store the date instead. To increment letters, you may have to create a function for it.
 

Ranman256

Well-known member
Local time
Today, 11:07
Joined
Apr 9, 2015
Messages
4,339
use an autonum field and a date field.
No need to program anything.
 

plog

Banishment Pending
Local time
Today, 10:07
Joined
May 11, 2011
Messages
11,613
Every week we get someone trying to invent a new numbering system. Evertime we try to talk them out of it and instead use autonumbers. They are unique and the system does it all for you. What's the purpose of your number system and why won't autonumbers work?

Also, if you do go through with your system what happens next year? You're going to be creating duplicate ID values--every year you would get a 125a record, how would you know which one is which?
 

Recons

New member
Local time
Today, 15:07
Joined
May 4, 2020
Messages
6
Hi Andris. Welcome to AWF!

I would recommend to leave them separate. You don't even need the Julian date part, because you know how to calculate it from a date, so just store the date instead. To increment letters, you may have to create a function for it.
Hi,

Unfortunately I can't leave it separately that's company requirements and this is what we have in all documentation, traceability etc.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 08:07
Joined
Oct 29, 2018
Messages
21,359
Unfortunately I can't leave it separately that's company requirements and this is what we have in all documentation, traceability etc.
Hi, What you store in the table doesn't have to be what you display on your documentation. You can easily combine fields together in queries, forms, or reports.
 

Recons

New member
Local time
Today, 15:07
Joined
May 4, 2020
Messages
6
Every week we get someone trying to invent a new numbering system. Evertime we try to talk them out of it and instead use autonumbers. They are unique and the system does it all for you. What's the purpose of your number system and why won't autonumbers work?

Also, if you do go through with your system what happens next year? You're going to be creating duplicate ID values--every year you would get a 125a record, how would you know which one is which?
Hi,
I guess it's not about the inventing but complying with current system. I know that this is outdated, but to go with simple autonumber I would need to spend hours in requesting amendments in the current system what is complicated because its different department and this would take ages them to sort out.
Next year I would need to create new database and start from blank sheet.
 

plog

Banishment Pending
Local time
Today, 10:07
Joined
May 11, 2011
Messages
11,613
With your prior 2 responses, you've put us in a difficult position. We want to remain polite and helpful, but those answers demonstrate you're out of your league trying to build a database. I honestly don't think you are up to this task, and to get there will take a few months of learning the basics of databases.

Unfortunately I can't leave it separately that's company requirements

We aren't talking about storing one part of your ID in a file in Australia and another part of the ID on a disk in Canada. We are talking about storing 2 pieces of data in the same record.

Next year I would need to create new database and start from blank sheet.

That's not how databases work. A database shouldn't needed to be cleaved once a certain threshold has been reached. It should be built to all accomodate data regardless of arbitrary limits.

I suggest you put this down and start reading a few tutorials. I would start with normalization (https://en.wikipedia.org/wiki/Database_normalization) then move to SQL (https://www.w3schools.com/sql/) then I would start in building your database and looking up specific MS Access issues you encounter.
 

Recons

New member
Local time
Today, 15:07
Joined
May 4, 2020
Messages
6
With your prior 2 responses, you've put us in a difficult position. We want to remain polite and helpful, but those answers demonstrate you're out of your league trying to build a database. I honestly don't think you are up to this task, and to get there will take a few months of learning the basics of databases.



We aren't talking about storing one part of your ID in a file in Australia and another part of the ID on a disk in Canada. We are talking about storing 2 pieces of data in the same record.



That's not how databases work. A database shouldn't needed to be cleaved once a certain threshold has been reached. It should be built to all accomodate data regardless of arbitrary limits.

I suggest you put this down and start reading a few tutorials. I would start with normalization (https://en.wikipedia.org/wiki/Database_normalization) then move to SQL (https://www.w3schools.com/sql/) then I would start in building your database and looking up specific MS Access issues you encounter.
Hi,

I hope I have not confused you about my request this combination of 3 digits and letter is our trace code and its only one of many table fields what will further go to a label/tag for fast tracking in case if there is an issue. Its might not be a VBA coding but like theDBguy said a another form, queries who combines this.
I came here because I seen some VBA codes with alphabetical letter increament but all of them was for the unique request and I thought one of you might have idea how I could make this working.
 
Last edited by a moderator:

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 02:07
Joined
Jan 20, 2009
Messages
12,849
Hi. Check out this link. Hopefully, it can give you some ideas.

This function works well for one letter but gets very clumsy after that.

A more general solution is a surprising complex task unless you understand the mathematical principles behind it. However you are in luck because it has already been done. The functions at the link below work on any positive long integer so will get you past decimal two billion which should be enough. ;)

The Base10 number to letter conversion functions are in the code repository at the gUtterAccess site. The first two functions convert back and forwards between a base 10 number and base 26 expressed as letters (the letter format you are wanting).

The third function increments the letter expression by using the functions to convert the letters to base 10, incrementing that number, then converting back to letters. (It doesn't sound very efficient but would be a very complex task to perform directly on the letters.)

BTW I wrote the code back in 2012 when I was still a member of gUtterAccess, based on a concept originally by another user there (doctor9). The original implementation was awful, using iteration to come up with the result while my solution cut to the chase using logs and slashed forty percent out of the time it took to run.

You can see the history if you look in the Options on the page. Note that CyberCow's "contribution" was solely to remove the authorship attribution that referred to doctor9 and myself. They have a hide to demand that anyone using the code includes the attribution to their site. (My objection to the removal of the authorship was part of what led to me getting banned there.)
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:07
Joined
May 7, 2009
Messages
19,175
you can also use the format:

julian-series(format "0000")

or (sample)

126-0001

this series is easy to implement rather than "A" increment as in Excel column:
Code:
Public Function NextSeries() As String
    Dim julian As String
    Dim c As Variant
    julian = Format(DatePart("y", Date), "000") & "-"
    c = DMax("theAutoGenField", "theTable", "Left(theAutoGenField,4)='" & julian & "'") & ""
    If c <> "" Then
        c = Format(Val(Replace(c, julian, "")) + 1, "0000")
    Else
        c = "0001"
    End If
    NextLetter = julian & c
End Function
 

HalloweenWeed

Member
Local time
Today, 11:07
Joined
Apr 8, 2020
Messages
213
I did something similar to this (only one letter though), and I used another field for the letter, where a third field had the concatenated string (whole thing). Specifically, I wanted to be able to differentiate between serial numbers if two or more documents were accidentally given the same number, so the letter(s) were a suffix. When displaying them, I used two textboxes, the number justified right, and the suffix justified left, where the textboxes bordered each other so they looked as if they were one box. Alternately, I could merely display the third field. I created a subroutine that would automatically add and increment the suffix as-needed, based on previous records, to prevent a repeat of the exact same third field (concatenated string).
 

Solo712

Registered User.
Local time
Today, 11:07
Joined
Oct 19, 2012
Messages
828
you can also use the format:

julian-series(format "0000")

or (sample)

126-0001

this series is easy to implement rather than "A" increment as in Excel column:
Code:
Public Function NextSeries() As String
    Dim julian As String
    Dim c As Variant
    julian = Format(DatePart("y", Date), "000") & "-"
    c = DMax("theAutoGenField", "theTable", "Left(theAutoGenField,4)='" & julian & "'") & ""
    If c <> "" Then
        c = Format(Val(Replace(c, julian, "")) + 1, "0000")
    Else
        c = "0001"
    End If
    NextLetter = julian & c
End Function

Why wouldn't you give the gent what he wants? It's not that much of a big deal.

Code:
Public Function GetJulianInc() As String
   Dim jl As String, ljl As String, hival As String, lchar As String, acode As Byte
 
   jl = Format(DatePart("y", Date), "000")
  
   hival = DMax("jul", "Jtable"): ljl = Left(hival, 3)
  
   'rollover
   If ljl <> jl Then
      GetJulianInc = jl & "a"
      Exit Function
   End If
  
   lchar = Right(hival, 1)
  
   If lchar = "z" Then
      GetJulianInc = hival & "a"
   Else
      acode = Asc(lchar)
      GetJulianInc = Left(hival, Len(hival) - 1) & Chr(acode + 1)
   End If

End Function

This function returns the next code in the format required.

Best,
Jiri
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:07
Joined
May 7, 2009
Messages
19,175
Why wouldn't you give the gent what he wants? It's not that much of a big deal.
I've seen your code. unfortunately it will always start with "z", "zz", "zzz", etc. whenever it reaches the "z".

here is another code. this time in format: julian + alpha
Code:
Public Function NextSeries() As String
    Dim julian As String
    Dim c As Variant
    julian = Format(DatePart("y", Date), "000")
    c = DMax("theAutoGenField", "theTable", "Left(theAutoGenField,3)='" & julian & "'") & ""
    If c = "" Then
        c = "a"
    Else
        c = Dec2Alpha(Alpha2Dec(Replace(c, julian, "")) + 1)
    End If
    NextSeries= julian & c
End Function


' https://stackoverflow.com/questions/12796973/function-to-convert-column-number-to-letter
'Function ColumnLetter(ColumnNumber As Long) As String
Function Dec2Alpha(ByVal ColumnNumber As Long) As String
Dim n As Long
Dim c As Byte
    Dim s As String
    n = ColumnNumber
Do
c = ((n - 1) Mod 26)
'uppercase
s = Chr(c + 65) & s
n = (n - c) \ 26
Loop While n > 0
Dec2Alpha = LCase(s)
End Function
' arnelgp
Public Function Alpha2Dec(ByVal s As String) As Long
Dim ln As Integer
Dim i As Integer
Dim r As Long

ln = Len(s)
s = UCase(s)
For i = ln To 1 Step -1
'uppercase
r = r + ((Asc(Mid(s, i, 1)) - 64) * (26 ^ (ln - i)))
Next
Alpha2Dec = r
End Function

so if you have "127a" in your table, calling NextSeries() function will return "127b".
if "127z" on the table, the NextSeries is "127aa" (not "127za", as in Post #14).
 
Last edited:

Recons

New member
Local time
Today, 15:07
Joined
May 4, 2020
Messages
6
I've seen your code. unfortunately it will always start with "z", "zz", "zzz", etc. whenever it reaches the "z".

here is another code. this time in format: julian + alpha
Code:
Public Function NextSeries() As String
    Dim julian As String
    Dim c As Variant
    julian = Format(DatePart("y", Date), "000")
    c = DMax("theAutoGenField", "theTable", "Left(theAutoGenField,3)='" & julian & "'") & ""
    If c = "" Then
        c = "a"
    Else
        c = Dec2Alpha(Alpha2Dec(Replace(c, julian, "")) + 1)
    End If
    NextSeries= julian & c
End Function


' https://stackoverflow.com/questions/12796973/function-to-convert-column-number-to-letter
'Function ColumnLetter(ColumnNumber As Long) As String
Function Dec2Alpha(ByVal ColumnNumber As Long) As String
Dim n As Long
Dim c As Byte
    Dim s As String
    n = ColumnNumber
Do
c = ((n - 1) Mod 26)
'uppercase
s = Chr(c + 65) & s
n = (n - c) \ 26
Loop While n > 0
Dec2Alpha = LCase(s)
End Function
' arnelgp
Public Function Alpha2Dec(ByVal s As String) As Long
Dim ln As Integer
Dim i As Integer
Dim r As Long

ln = Len(s)
s = UCase(s)
For i = ln To 1 Step -1
'uppercase
r = r + ((Asc(Mid(s, i, 1)) - 64) * (26 ^ (ln - i)))
Next
Alpha2Dec = r
End Function

so if you have "127a" in your table, calling NextSeries() function will return "127b".
if "127z" on the table, the NextSeries is "127aa" (not "127za", as in Post #14).



I guess I'm doing something wrong, I have Run-time error '2485'. I have added your codes and trying to run through Forms On Click
"DoCmd.RunMacro NextSeries". The object "127d" would be next on the table so why does it triggers error ?.

1588760696275.png

Many thanks
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:07
Joined
May 7, 2009
Messages
19,175
here is a sample.
start typing firstname in new record.
see BeforeInsert event of the form.
see NextSeries function specially the Table and Fieldname.
 

Attachments

  • baseLetter.zip
    43.2 KB · Views: 126

Recons

New member
Local time
Today, 15:07
Joined
May 4, 2020
Messages
6
here is a sample.
start typing firstname in new record.
see BeforeInsert event of the form.
see NextSeries function specially the Table and Fieldname.

Thank you very much! You are a star!
I have adjusted my database and all works now as on your sample.
 

Solo712

Registered User.
Local time
Today, 11:07
Joined
Oct 19, 2012
Messages
828
I've seen your code. unfortunately it will always start with "z", "zz", "zzz", etc. whenever it reaches the "z".

here is another code. this time in format: julian + alpha
Code:
Public Function NextSeries() As String
    Dim julian As String
    Dim c As Variant
    julian = Format(DatePart("y", Date), "000")
    c = DMax("theAutoGenField", "theTable", "Left(theAutoGenField,3)='" & julian & "'") & ""
    If c = "" Then
        c = "a"
    Else
        c = Dec2Alpha(Alpha2Dec(Replace(c, julian, "")) + 1)
    End If
    NextSeries= julian & c
End Function


' https://stackoverflow.com/questions/12796973/function-to-convert-column-number-to-letter
'Function ColumnLetter(ColumnNumber As Long) As String
Function Dec2Alpha(ByVal ColumnNumber As Long) As String
Dim n As Long
Dim c As Byte
    Dim s As String
    n = ColumnNumber
Do
c = ((n - 1) Mod 26)
'uppercase
s = Chr(c + 65) & s
n = (n - c) \ 26
Loop While n > 0
Dec2Alpha = LCase(s)
End Function
' arnelgp
Public Function Alpha2Dec(ByVal s As String) As Long
Dim ln As Integer
Dim i As Integer
Dim r As Long

ln = Len(s)
s = UCase(s)
For i = ln To 1 Step -1
'uppercase
r = r + ((Asc(Mid(s, i, 1)) - 64) * (26 ^ (ln - i)))
Next
Alpha2Dec = r
End Function

so if you have "127a" in your table, calling NextSeries() function will return "127b".
if "127z" on the table, the NextSeries is "127aa" (not "127za", as in Post #14).
You are right, my bad. :)

Best,
Jiri
 

Solo712

Registered User.
Local time
Today, 11:07
Joined
Oct 19, 2012
Messages
828
One more thing, Arnel. The back-and-forth conversion btw asc numerics and letters is what is called for but your method has a problem on this line:
Code:
 c = DMax("theAutoGenField", "theTable", "Left(theAutoGenField,3)='" & julian & "'") & ""
Since you are comparing strings of unequal length you cannot use DMax. It would evaluate "aa" > "z" as False, and the series would get stuck there.
What should be used is
Code:
 c = DLast("theAutoGenField", "theTable", "Left(theAutoGenField,3)='" & julian & "'") & ""
but a word of caution is in order here. This will only work if the table has a primary key like autonumber, one that is always ascending. theAutoGenField cannot be the primary key of the table.
Best,
Jiri
 

Users who are viewing this thread

Top Bottom