asked on

Sorting records alphabetically in VB.net

Hi,
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 VB.net application), I need this VB.net 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 VB.net 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 VB.net.

SOLUTION

Scott McDaniel (EE MVE )

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

TWBit

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".

ASKER CERTIFIED SOLUTION

Sancler

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

BozM

ASKER

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.