Auto increment of numbers with alphabets

[Shiva Kemkar]

Hello,

i want to create a automatic change number while increment the number as well as the alphabet. I want to do this in ms access. Kindly support
Eg.
For the year 2018
A002AA414A
A002AA415A
A002AA416A
A002AA417A

For the year 2019 (only the last character A changes to B)
A002AA414B
A002AA415B
A002AA416B
A002AA417B

 

[Ranman256]

what happens after Z?

 

[Shiva Kemkar]

should end at Z. alphabet on the right increments only on a yearly basis.
like 2018 - A, 2019 - B and so on .....

 

[plog]

What happens in 2018 after record A002AA999A? Why must these numbers reset every year? And you didn't answer Ranman--what letter is going to be used in 2034? Or 2035 if I am off by one?

A lot of numerologists come to this site thinking that ID numbers have mystical intrinsic powers and their business needs "special" ones. They do not. Most likely an autonumber is the best way to go and you need to forget your hidden-meaning ID numbers. What exactly do these special numbers do for you? Why must they conform to this format you have?

 

[Shiva Kemkar]

I don't belong there, it is a guideline to be followed. I will have to create a unique change number based on the guidelines. I am even not sure if I would receive 999 changes for next 3 years.for now the focus is only for three years. Thanks

 

[The_Doc_Man]

In general, when facing this type of complex ID number, particularly when mandated by "the powers that be", the best solutions I have seen involve building a public function in a general module. Then you can use it in a query or call it from VBA code, either way that you would need it.

The down side of this is that you cannot directly import to the final table because of the "impossible" autonumber format. You have to import from a temporary table so that you can use an INSERT INTO or UPDATE or some VBA code to assert the new sequence number first, then do the final action via some variant of INSERT INTO...FROM syntax.

Of course, if you input data using a form, the form can call the function when storing the new record, probably during the BEFORE UPDATE event code if not sooner.

 

[Shiva Kemkar]

As i am new to ms access and VBA, request you to post some code which can help to achieve the expected result. sql query or form input should be fine or which ever is less complex.

Thanks all

 

[The_Doc_Man]

Before anyone could reasonably post code, we would need to know which parts of that string vary and how you know that they could vary. Not just that last digit, but the other parts as well. Your description only shows us the last four characters varying.

 

[Shiva Kemkar]

below table gives more clarify of the requirement. Hope this helps

CONSTANT A 0 0 2 A A RUNNING NUMBER
4 1 4 CHANGES ONLY IN THE BEGINNING OF THE YEAR (the last character)
A

 

[Shiva Kemkar]

something went wrong with my table, i am posting it again

Constant numbers:
A002AA

Running numbers:
414

Changes only in the beginning of the year
A (the last character)

 

[The_Doc_Man]

A few further questions.

1. Must this "running number" always be 3 digits?

2. If so, are we talking about leading zeroes until you have one hundred of them?

3. What happens if you have an unusually / unexpectedly busy year and have more than 999 running numbers? Can that happen? More precisely, what do you have to do WHEN it happens. Notice I didn't say IF because we all know that in any project, the unexpected is expected to occur pretty much just when it would hurt you the most - a corollary of Murphy's Laws.

 

[Shiva Kemkar]

please find the answers below:

1. yes it will start at 001 and end at 999. the current number is 414
2. yes
3. i don't see a possibility until next 5 years . so i think it's fine to consider the maximum as 999.
Thanks

 

[arnelgp]

copy and paste the code below.
you can call it in a Select query as expression or Update query.
or on a textbox as controlsource:

select fncSerial() as Expr1 from yourTable;

for textbox, controlsource:

=fncSerial()

or on beforeInsert event of the form

Private Sub Form_BeforeInsert()
Me.textbox = fncSerial()
End Sub

Option Compare Database
Option Explicit

Public Function fncSerial() As String

    Dim strLastChar As String
    Dim varValue As Variant
    Dim intSeries As Integer
    '*
    '* replace with correct table name and fieldname
    '*
    Const TABLE_NAME As String = "PutTableNameHere"
    Const FIELD_NAME As String = "PutFieldNameHere"
    
    strLastChar = Chr(Asc("A") + Year(Date) - 2018)
    fncSerial = "A002AA414" & strLastChar
    If DCount("1", TABLE_NAME) <> 0 Then
        varValue = DMax(FIELD_NAME, TABLE_NAME, "RIGHT(" & FIELD_NAME & ", 1)=""" & strLastChar & """")
        If Not IsNull(varValue) Then
            intSeries = Val(Mid(varValue & "", 7, 3)) + 1
            fncSerial = Left(varValue & "", 6) & intSeries & strLastChar
        End If
    End If
End Function

 

[June7]

Consider:

Public Function NewID() As String
    Dim strID As String, x As String
    strID = Nz(DMax("fieldname", "tablename"), "")
    x = Switch(Year(Date) = 2018, "A", Year(Date) = 2019, "B", Year(Date) = 2020, "C")
    If Right(strID, 1) = x Then
        'increment the sequence
        NewID = Left(strID, 6) & Format(Mid(strID, 7, 3) + 1, "000") & x
    Else
        'restart sequence
        NewID = "A002AA001" & x
    End If
End Function

Edit: Now I see arnelgp code. Like the use of Asc() and Chr() functions to get the letter suffix for the current year.

 

[Shiva Kemkar]

1. i have created a module and pasted the function
2. the textbox is linked by control source
3. The data type of the change number is set to short text

i get error in the text box as #Name?. unable to figure out as what is the issue

 

[June7]

Which function? I did some edits on my suggestion. Did you use your field and table names where appropriate?

Calling from textbox ControlSource will not save into table. Requires VBA in some event to save into record. The real trick is figuring out what event to use.

Me!fieldname = NewID()

 

[Shiva Kemkar]

yes, i have used the field and the table name. which is as below.
Field name: change_no
table name: change_number

i am new and not sure where to copy the code.

1. I have a form with the filed change_no
2. on form load before insert event i have the below mentioned code
Private Sub Form_BeforeInsert(Cancel As Integer)
Me.Change_No = NewID()
End Sub
3. i have put your code (june 7) in the module.
but i see the form blank now. my table has no value stored

 

[arnelgp]

Try adding some record.
Also set the Locked property of change_no textbox to Yes so it wont allow editing, only through code.

 

[Shiva Kemkar]

sorry. does not work, all that i have did is mentioned in my previous thread. not sure what's wrong
the control source is set to Change_No
ok, i have locked the textbox

 

[arnelgp]

Did you change the fieldname and tablename on the functuon with your table and fieldname?