Auto increment of numbers with alphabets (1 Viewer)

Shiva Kemkar · May 16, 2018

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 · May 16, 2018

what happens after Z?

Shiva Kemkar · May 16, 2018

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

plog · May 16, 2018

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 · May 16, 2018

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 · May 16, 2018

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 · May 17, 2018

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 · May 17, 2018

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 · May 17, 2018

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 · May 17, 2018

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 · May 17, 2018

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 · May 17, 2018

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 · May 17, 2018

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

Code:

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 · May 17, 2018

Consider:

Code:

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 · May 17, 2018

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 · May 17, 2018

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 · May 17, 2018

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 · May 17, 2018

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 · May 17, 2018

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 · May 17, 2018

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

Auto increment of numbers with alphabets (1 Viewer)

Registered User.

Well-known member

Registered User.

Banishment Pending

Registered User.

Immoderate Moderator

Registered User.

Immoderate Moderator

Registered User.

Registered User.

Immoderate Moderator

Registered User.

..forever waiting... waiting for jellybean!

AWF VIP

Registered User.

AWF VIP

Registered User.

..forever waiting... waiting for jellybean!

Registered User.

..forever waiting... waiting for jellybean!

Similar threads

Users who are viewing this thread