Numbers to a letter

[snipe]

Hey guys,



We have employee codes in our system, i.e. 07173 is mine. Our new system requires the first character of a employee code to be a letter. The powers at be have decided to convert the first 2 numbers to a letter. 00=A, 01=B, ... , 25=Z. My code, 07173 would be H173. I am aware that this solution will fail eventually, but in the 12 years of business, we haven't gone thru 10000 employees yet, so we should be good for a while. Is there an easier way to do this besides a big Select Case formula that goes thru the alphabet?

I know I would have to make that formula go thru the first, say, 15 letters to be safe for a while. But I am more curious for my own knowledge.

[ItsMe]

What is the purpose? Is it to convert all the existing codes to alphanumeric and then, moving forward, fill in all of the (left over) possible AXXX before moving to B?

Or is the purpose to use the old way of assigning employee codes and simply display them using this Alpha key?

[June7]

Build a table. A query can have calculated field that extracts the first 2 digits from the old code and another calculated field to extract the other 3 digits. Join this query to the table. Concatenate the alpha prefix with the 3 digit field. Run UPDATE query to edit existing field or populate new field in Employees table.

Is the employee code a PK/FK?

[snipe]

Got it, June7. Thanks!