Turning Letters in to Numbers (code)

[Jboymeng]

Hi everyone!

Hope Everyone is good.

I have this UDF in my excel template that changes a set of numbers in to letters corresponding a code.

For example the code is "EUCHARISTO" it would simply mean
E=1, U=2, C=3, H=4, A=5, R=6, I=7, S=8, T=9, O=0 and by default the tenths position (".0")=X the hundredths position (.0"0") = Y and If a number repeats it becomes G.

Examples
12.50 = EUAY
123.00 = EUCXY
12.25 = EUGA
99.00 = TGXY
99.50 = TGAY
999.00 = TGTXY
999.99= TGTGT

Here's the UDF:

Function LetterCode(ByVal Numbers As String, Letters As String) As String
  Dim X As Long
  Numbers = Format(Numbers, "0.00") * 100
  Letters = UCase(Right(Letters, 1) & Left(Letters, Len(Letters) - 1))
  If Numbers Like "*0" Then Mid(Numbers, Len(Numbers)) = "Y"
  If Numbers Like "*0?" Then Mid(Numbers, Len(Numbers) - 1) = "X"
  For X = 1 To Len(Numbers)
    If X > 1 Then If Mid(Numbers, X, 1) = Mid(Numbers, X - 1, 1) Then Mid(Numbers, X) = "Z"
    If Mid(Numbers, X, 1) Like "#" Then
      LetterCode = LetterCode & Mid(Letters, Mid(Numbers, X, 1) + 1, 1)
    Else
      LetterCode = LetterCode & Mid(Numbers, X, 1)
    End If
  Next
End Function

Also, I need advice where best to put this code, In a query or in a module.

Thank you all in advance!
Have a nice day!

 

[gemma-the-husky]

so what is the problem?

 

[Jboymeng]

I cant get the module to work in MS Access. How do I do that?

 

[Jboymeng]

Somebody Please help.

How to do use this as an ms access function?

 

[marlan]

http://www.techonthenet.com/access/modules/index.php

 

[gemma-the-husky]

try this - just change replace to the string you want to use for the numbers.
the tenth letter is 0, the 11th is the decimal character

 Function LetterCode(Numbers As Variant) As String
'number as variant allows for both whole and decimal numbers
 
Const replace = "ABCDEFGHIJ."
Dim nstrg As String
Dim x As Long
Dim newch As String

  nstrg = CStr(Numbers)
 'now substitute the letters
 For x = 1 To Len(nstrg)
     Select Case Mid(nstrg, x, 1)
    Case "1": newch = Mid(replace, 1, 1)
    Case "2": newch = Mid(replace, 2, 1)
    Case "3": newch = Mid(replace, 3, 1)
    Case "4": newch = Mid(replace, 4, 1)
    Case "5": newch = Mid(replace, 5, 1)
    Case "6": newch = Mid(replace, 6, 1)
    Case "7": newch = Mid(replace, 7, 1)
    Case "8": newch = Mid(replace, 8, 1)
    Case "9": newch = Mid(replace, 9, 1)
    Case "0": newch = Mid(replace, 10, 1)
    Case ".": newch = Mid(replace, 11, 1)
    End Select
    Mid(nstrg, x, 1) = newch 'mid used in this way to substitute the character
 Next

 LetterCode = nstrg

 End Function

?LetterCode(1234.56)
ABCD.EF


you will have to play around with it, to get to your idea for repeating characters, etc

 

[namliam]

This looks awfully familiar somehow..

Only difference in your code it says "Z" for repeating chars where you mention G for that.
Other than that you should be able to paste this straight into a module and use it as a function in a query, form or report without any problems.