Here's some quickie code and a query to do it.
If you want to add the result to the table, add a field to the table to hold
the conversion and call the code from the query, like this . YES. you can
call code from querys.
UPDATE Table1 SET Table1.NewValue = ChangeTheValue([OrigValue]);
Add this code to a module.
Function ChangeTheValue(Orig As String)
Dim x As String
Dim y As String
Dim xy As String
x = UCase(Right(Orig, 1))
' here is the long version for easy conversion to your letter to number
conversion.
Select Case x
Case "A"
y = "1"
Case "B"
y = "2"
Case "C"
y = "3"
Case "D"
y = "4"
Case "E"
y = "5"
Case "F"
y = "6"
Case "G"
y = "7"
Case "H"
y = "8"
Case "I"
y = "9"
Case Else ' if no number add a "0"
y = "0"
End Select
If y <> "0" Then
xy = Left(Orig, Len(Orig) - 1) & y
Else
xy = Orig & y
End If
' use this line to return a number
ChangeTheValue = Val(xy) / 100
' use this line to return a string
' ChangeTheValue = Left(xy, Len(xy) - 2) & "." & Right(xy, 2)
End Function
Mike Schlosser
