How do you obtain the next letters up in the alphabet from existing data?

[Maz2410]

I have a field in a vehicle table that is a unique code consisting of a 3 digit number and 2 letters. So it looks like this:-
eg.
003AA
003AB
003AC
021JA
021JB
021JC

I have another table that holds new records that I eventually want to add to the first table but the field in the second table only contains the 3 digit number part of this unique code. So the second table looks like this:-

Code, Make, Model
003, AUDI, A4 TDI QUATTRO SPORT
003, AUDI, A4 CABRIOLET
021, FORD, SIERRA LX
021, FORD, SIERRA GLX AUTO

etc...

I want to create a query or something to search the first table for the code on the second table and where I find the 3 digit number (eg. 003), I want to find the next unique code up that I can use, that is not already in use. So I would want it to find new code 003AD for the first vehicle of the example above, because 003AA, 003AB and 003AC are all already in use for other vehicles.

I have no idea how to do this automatically but I am tired of doing this as a manual exercise.

I would really appreciate any help or ideas... Thank you

[dsatino]

I feel like there's a way to do this with character codes, but I can't think of how.

A quick work around would be to simply create a reference table where each letter has a numeric value. Capture the last letter of the string, look up it's numeric value,add one, and then lookup the related letter.

Or something of that nature, but you get the general idea

[ADezii]

I literally threw this together before going out, but it should at least give you a starting point to reference. Simply pass to the Function a 3-Digit Base Code for which you want generate the next, sequential Code based on those 3-Digits. I listed some Sample Data along with Results, but first a few Assumptions.

1. Your Table name is tblCodes and consists of a Field named [Code2](TEXT}. Any other Fields in this Table are irrelevant.
2. The Code always follows the same exact Format (3-Digits, 2 Strings), and is sequential in nature as in: 045AA, 045AB, 045AC, 045AD, 076AA, 076AB, etc.
3. You will never have a situation where 3 Digits would be followed by a Double 'Z', namely XXXZZ. If you would, the code will have to be modified. For now, it allows for a 'Z' on the back end, and makes the proper adjustments.
4. Function Definition:
   

   Public Function fRetrieveNextAvailCode(strCode As String) As String
   Dim MyDB As DAO.Database
   Dim rst As DAO.Recordset
   Dim strNewCode As String
    
   Set MyDB = CurrentDb
    
   'Retrieve only the Codes that begin with the 3 Characters in strCode,
   'namely 001, 002, 003, 021, 099, 124, etc.
   Set rst = MyDB.OpenRecordset("SELECT * FROM tblCodes WHERE Left$([Code2], 3) = '" & _
                                 strCode & "'", dbOpenSnapshot)
    
   With rst
     If .BOF And .EOF Then     'No Records exist for 3-Digit Code
       MsgBox "No 2-Digit Base Code beginning with [" & strCode & "]", vbExclamation, "No Records"
         .Close: Set rst = Nothing: Exit Function
     End If
    
     .MoveLast     'Retrieve Last Code used (003AD, 098GH, etc.)
    
     If Right$(![Code2], 1) = "Z" Then        'Special case, set Last Character to 'A', then
                                           'Increment 1st Character by 1 Letter
       strNewCode = Left$(![Code2], 3) & Chr$(Asc(Mid$(![Code2], 4, 1)) + 1) & "A"
     Else      'Increment Last Character by 1
       strNewCode = Left$(![Code2], 4) & Chr$(Asc(Right$(![Code2], 1)) + 1)
     End If
   End With
    
   rst.Close
   Set rst = Nothing
    
   fRetrieveNextAvailCode = strNewCode
   End Function
    

5. Sample Data (tblCodes):
   

   Code2
   003AA
   003AB
   003AC
   003AD
   003AE
   003AF
   004AA
   004AB
   088KZ

6. Function Calls along with results:
   

   Debug.Print fRetrieveNextAvailCode("003")
   003AG

   Debug.Print fRetrieveNextAvailCode("004")
   004AC

   Debug.Print fRetrieveNextAvailCode("088")
   088LA

P.S. If you really wanted to be trick, you can now easily create a Query which will show the next Code in sequence for the Unique 3-Character Codes in your 2nd Table.

[Maz2410]

Hi ,

Thank you very much - that looks brilliant.

I think I understand what it is doing.

The only thing is that this code doesn't compile at the beginning when it sets "dim mydb as dao.database" because I don't think I have the DAO option of Database perhaps?

It is saying it does not recognise the user defined type "database".

How do I define the database?

Thank you very much for your help.

[NeoPa]

It needn't be as complicated as to require a code solution.

Asc() & Chr() are functions that can be used to increment a character from one to the next.

DMax() is a Domain Aggregate function that can find the largest value so far in your table that matches the criteria, which should specify that the code field starts with the numeric string value you're interested in.

[ADezii]

Wouldn't it be a little sticky if the last Character was a 'Z' which would now require a wrap-around on the last Character to 'A', and an increment of the previous one? If the last 2 Characters were 'ZZ', then I see nothing but a code based solution.

[ADezii]

Should you decide to use this approach, you may need to Set a Reference to the Microsoft DAO X.X Object Library.

[NeoPa]

It's a fair point ADezii, but my reading of the question, which could be wrong of course, is that the fourth character is not a progression from A upwards after overrunning the alphabet, but a code assigned to the first three numbers. Looking at the example data posted (and reading between the lines) that data is static and no allowance has been made (nor needs to probably) for any necessity to handle an overflow of the character Z.

Obviously much is conjecture due to the lack of detail in the question, but I see nothing that indicates to me that such handling would be required for this particular question.

Clearly the OP would have the final say on the whole issue of course :)

[Maz2410]

Hello,

Sorry - I did not put enough detail in the original question.

Yes, if the last character is a Z, then, yes I do require it to start with the next alphabet letter followed by an A - eg. after AZ, I would want it to find BA as the next code, which is what it does in the code you posted.

Also, when it gets to ZZ then it has run out of codes at which point I think I just want it to error to alert me that it has run out of codes.

So I think the code does what I want. I just need to get it to access the DAO library otherwise it won't work.

I'll have another go at doing that...

Is it just a statement that I have to put in?

or could it be possible that the DAO library is not installed in my version of Access? I'm not quite sure how it works....

Thank you for all your help...

[NeoPa]

If you go into the VBA editor (IDE) then you can set the references required or the database from the Tools menu. You would be looking to select one of the form :
Microsoft DAO x.x Object Library

If you're using ADezii's code, don't forget to set his post as the Best Answer ;)

[Maz2410]

Thank you - I set the reference in Tools menu and that works.

The only thing is that I now have a further problem.

My table that contains the new records consisting of the 3 digit number code contains many of the same vehicle makes and they all have the same code - eg:-

003, AUDI, A4 TDI QUATTRO SPORT
003, AUDI, A4 CABRIOLET
282, VAUXHALL, AGILA S
282, VAUXHALL, AGILA SE
282, VAUXHALL, INSIGNIA EXCLUSIV 4X4

etc

When I run the code as it is, it assigns the same new code to all the Vauxhalls so they are all assigned
'282BE' for example when I wanted each one to have a different ascending alphabet code.

This is because I am calling it from a 'make table' query and I am not adding each new code each time to the original table that the code is reading so it doesn't know about each new code. Should I call it from a different type of query like an append query so it keeps adding to the same table? Would that work?

What would be the best way of doing it?

Thank you for your help...

[NeoPa]

I suspect that's down to optimisation. If you sopecify a function call in your SQL the the SQL engine will try to determine if it needs to run the function every time a record is processed, or whether it can get away with calling it just the once up front, and remembering the returned value for use in subsequent calls.

Usually, if the parameter(s) passed to the query are record related then it will call it each time. If the parameter(s) are literal values the SQL engine will assume it is the same value required and simply remember it for other records.

If you'd like to paste in a copy of your MakeTable SQL we can check it for you and probably suggest an alternative way of calling the function so that it executes for each record.

[Maz2410]

Hello,

I'm pretty sure that it is executing the code for each record because the Make Table query has created a new table with every record assigned a new value consisting of the new code - eg. 282BE. They are just all allocated the same code if they are the same 3 digit number.

I put a debug break in the code and I can see it going in each time and then coming out and then going back in with the next record etc.

It's just that each time a new value is assigned, it assigns the new value and puts it in the new table and then it goes back into the code with the next record and looks at the same original table to obtain the next alphabet code, so it obtains exactly the same code again.

So when it executes the following code :-

Set rst = MyDB.OpenRecordset("SELECT * FROM tblCodes WHERE Left$([Code2], 3) = '" & _ 
                              strCode & "'", dbOpenSnapshot)

the table "tblCodes" has not been updated with the new codes.

Here is my make table SQL where fRetrieveNextAvailCode is being called on the field CORNHILL CODE:-

SELECT New_Additions_from_Pricing.[ABI Code]
     , fRetrieveNextAvailCode(New_Additions_from_Pricing![CORNHILL CODE]) AS Code
     , New_Additions_from_Pricing.[BM Ind]
     , New_Additions_from_Pricing.Make
     , New_Additions_from_Pricing.cc
     , New_Additions_from_Pricing.Model
     , New_Additions_from_Pricing.Yrs
     , New_Additions_from_Pricing.Doors
     , New_Additions_from_Pricing.Fuel
     , New_Additions_from_Pricing.Transmission
     , New_Additions_from_Pricing.[Group Value 50]
     , New_Additions_from_Pricing.[Motor Cover Grp]
     , New_Additions_from_Pricing.[MotorCover SW]
     , New_Additions_from_Pricing.[MotorCover SW 50]
     , New_Additions_from_Pricing.[MotorCover Terms]
     , New_Additions_from_Pricing.[MotorCover Excess]
     , New_Additions_from_Pricing.[MotorCover Load]
     , New_Additions_from_Pricing.EffDateFrom
     , New_Additions_from_Pricing.EffDateTo
     , New_Additions_from_Pricing.DisplayInd
     , New_Additions_from_Pricing.CarplanGrp
     , New_Additions_from_Pricing.CarPlanTerms
     , New_Additions_from_Pricing.[CarPlan SW]
     , New_Additions_from_Pricing.[CarPlan SW 50]
     , New_Additions_from_Pricing.HorizonGrp
     , New_Additions_from_Pricing.[Horizon Terms]
     , New_Additions_from_Pricing.[Horizon Excess]
     , New_Additions_from_Pricing.[IT Grp]
     , New_Additions_from_Pricing.[IT Terms]
     , New_Additions_from_Pricing.BD1Grp
     , New_Additions_from_Pricing.[BD1 Trms]
     , New_Additions_from_Pricing.[BD2 Grp]
     , New_Additions_from_Pricing.[BD2 Trms]
     , New_Additions_from_Pricing.[Car Multiplier]
     , New_Additions_from_Pricing.[Clear Peril Group]
     , New_Additions_from_Pricing.[Clear Combined Group]
     , New_Additions_from_Pricing.[Clear COMP XS]
     , New_Additions_from_Pricing.[Clear Terms] INTO New_Cornhill_Codes
FROM New_Additions_from_Pricing;

Thank you for your help...