Convert all letters in a table to a number

  • Thread starter Thread starter Jason
  • Start date Start date
J

Jason

I have an old access database with thousands of records and several
tables. We are converting to a new system. The problem with the new
system is that one of the fields will not recognize letters and the
old system used letters in that field. So what I would like to do is
convert all letters to a number. For example the letter 'A' would be
replaced by '1' 'B' --> '2' ..... 'Z' --> '26'.

Is this possible?

We are using Access 2003.
 
Jason

You could run an Update Query on the table to update YourNumberField to...
Asc([YourLetterField])-64
 
Jason,
You'll need to do an "Update" query against the table. (say your letter
field is called [Letter])
I would create a new field (Numeric Integer) in the table called...
[LetterToNumber]

Update [LetterToNumber] with the following UpdateTo criteria...
= Asc(Ucase([Letter]))-64

Then delete field Letter and rename LetterToNumber to the old field name.
"A" has an ascii value of 65, "B" has an ascii value of 64, etc... etc...
so we're using that to our advantage here.
hth
Al Camp
 
Is there any reason you can't just perform find and replace 26 times? It
would probably be quicker than figuring out a programatic way of doing it, or
finding a way to use an update query, or something of that sort.
 
BruceM said:
Is there any reason you can't just perform find and replace 26 times? It
would probably be quicker than figuring out a programatic way of doing it, or
finding a way to use an update query, or something of that sort.
Click to expand...
 
I frequently use chr(10) and chr(13) in code. I wonder why it never occurred
to me to make use of the rest of the ASCII character set. I would have just
slogged around with Find and Replace. New insights are a big part of the
value of these forums.

AlCamp said:
Jason,
You'll need to do an "Update" query against the table. (say your letter
field is called [Letter])
I would create a new field (Numeric Integer) in the table called...
[LetterToNumber]

Update [LetterToNumber] with the following UpdateTo criteria...
= Asc(Ucase([Letter]))-64

Then delete field Letter and rename LetterToNumber to the old field name.
"A" has an ascii value of 65, "B" has an ascii value of 64, etc... etc...
so we're using that to our advantage here.
hth
Al Camp

Jason said:
I have an old access database with thousands of records and several
tables. We are converting to a new system. The problem with the new
system is that one of the fields will not recognize letters and the
old system used letters in that field. So what I would like to do is
convert all letters to a number. For example the letter 'A' would be
replaced by '1' 'B' --> '2' ..... 'Z' --> '26'.

Is this possible?

We are using Access 2003.
Click to expand...
Click to expand...
 
this seems to work OK, but this is what I have. My Letter field is a
customerID and it has 2 letters and 4 numbers. I would like to convert
the first two letters to a number equivilant. ie. Old CustomerID =
ZZ1234 new CustomerID = 26261234. When I run the suggested query I
would only get 26. This would create hundreds of duplicates.
 
Jason,

Aha, the plot thickens! Ok, update CustomerID to...
Asc(Left([CustomerID],1))-64 & Asc(Mid([CustomerID],2,1))-64 &
Mid([CustomerID],3)
 
Back
Top