Convert all letters in a table to a number

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

 

[Steve Schapel]

Jason

You could run an Update Query on the table to update YourNumberField to...
Asc([YourLetterField])-64

 

[AlCamp]

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

 

[Guest]

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.

 

[jayscott1]

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.

 

[Guest]

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.

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

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.

 

[jayscott1]

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.

 

[Steve Schapel]

Jason,

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