Sorting records alphabetically in

I've got an MS Access table; each record has a column which gives that record's unique alphanumeric code e.g. A0001W, A002W, B001W etc. (The "W" at the end is constant.) When a user creates a new record in the table (through a application), I need this application to assign a new code to the new record based on the previous highest value for that letter. So if the Contact's name in the new record is George and the previous highest G code was G056W, I need the program to assign G057W to the new record. I'm using 2005 with Access XP. Any suggestions would be most welcome. I had something similar working just fine in VBA but now I'm at sea trying to achieve the same in

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Are you using a single column to store this? Generally this wouldn't be the best method; it appears that your "0001", "0002" is the "counter" section ... you should store this value for each record, then build the "unique alphanumeric code" as needed ...
Agreed the proper way would be to have an separate numeric field and build the code as needed.  In lieu of this, you could do it through code.
For your example, you need to isolate the number.  You could use something like Mid([uniqueID],2,InStrRev([uniqueID],"W")-2).  This will returen 0001, 002, 001, respectively.  Increase that result by 1.  Then concatenate the user's initial and the trailing "W".
You could use something like

   Imports System.Data.OleDb

        Dim conString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & <yourdatabasename>
        Dim con As New OleDbConnection(conString)
        Dim cmd As New OleDbCommand
        Dim Result As String

        Dim sql As String = "SELECT MAX(<yourfieldname>) FROM <yourtablename> WHERE <yourfieldname> LIKE 'G%'"
        cmd.Connection = con
        cmd.CommandText = sql
        Result = cmd.ExecuteScalar

Then Result would have in it the highest number so far used for 'G'.  You could extract the numerical portion with .SubString.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
BozMAuthor Commented:
Thanks lads, LSMConsulting gave me the common sense way to structure the database in the first place and Roger gave me the code to work around my current structure. Thanks to both of you.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.