converting numbers to alpha

  • Thread starter Thread starter Haigy
  • Start date Start date
H

Haigy

we have a database that has clientcodes from legacy systems in the form

01123
02123
......
27123

(yes z is 27 as mc has been given its own number)

i would like to change the first 2 letters to a,b etc

would like to do this from a query

thanks for help
 
Try this:

UPDATE MyTable
SET ClientCode =
Mid("a b c d e f g h i j k l m mcn o p q r s t u v w x y z ",
(CInt(Left([ClientCode], 2) * 2) -1, 2) & Mid([ClientCode], 3)

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
 
Back
Top