Change Numbers To Letters

[theserginhus]

I'd like to know if anybody has a formula that I could use to transform numbers into letters to use on my costing codes. Say I have the letters BLACKWHITE and I want to give it values like B1 L2 A3 C4 K5 W6 H7 I8 T9 E0, and whenever I enter a price like 500, I get code: KEE on my code field. Is it possible? And if I want to reverse, from letters to numbers? Anyone there with an answer? thanks...

 

[namliam]

But then what about BLACKANDWHITE you will run out off numbers

 

[raskew]

See if this will get you started.

Function AlphaNum(pCode As String, pNum As Variant) As String
'*******************************************
'Re:        [url]http://www.access-programmers.co.uk/forums/showthread.php?p=283918#post283918[/url]
'Coded By:  raskew
'Inputs:    from debug window:
'           ? alphanum("blackwhite", 21817)
'Output:    LBIBH
'*******************************************

Dim strOut  As String
Dim strHold As String
Dim intHold As Integer
Dim n       As Integer

    strHold = pCode
    For n = 1 To Len(pNum)
       intHold = Mid(pNum, n, 1)
       strOut = strOut & Mid(strHold, IIf(intHold = 0, 10, intHold), 1)
    Next n
    
    AlphaNum = UCase(strOut)

End Function

HTH - Bob

 

[theserginhus]

I just need 10 letters, for the numbers from 0 to 9.

But then what about BLACKANDWHITE you will run out off numbers

 

[theserginhus]

Letters to Numbers

I take it it will work, but since I am not familiar with coding, I am in trouble. I guess I need to explain that I am NEW to access and learning on my own. If you could insert this into my form, that will call the changes will be greatly appreciated. Thanks...


'*******************************************
'Re: http://www.access-programmers.co.uk/forums/showthread.php?p=283918#post283918
'Coded By: raskew
'Inputs: from debug window:
' ? alphanum("blackwhite", 21817)
'Output: LBIBH

 

[raskew]

Friends Don't Let Friends Do Macros

1) Go to the posted code. Highlight it then press (Ctrl + C) to copy it to the clipboard.

2) From the database window (Alt + F1) select the Modules tab, then click on New.

3) Press (Ctrl + V) to paste the code to the new module from the clipboard.

4) Escape from the module and when prompted to save, do so (name: Module1 is fine).

5) Move to the debug (immediate) window with (Ctrl + G). Type the following:

? alphanum("blackwhite", 21817)

6) Press enter.

With any luck at all, you'll be greeted with:

LBIBH

...which is the conversion you said you were looking for.

Experiment with changing the integer, e.g.

? alphanum("blackwhite", 500)

and stepping through the code line-by-line to see how the changes occur.

HTH - Bob

 

[pdx_man]

And to enhance this working code, I added the ability to go from code to #s.

Function AlphaNum(pCode As String, pNum As Variant, Direction As String) As String
'*******************************************
'Re:        [url]http://www.access-programmers.co.uk...3918#post283918[/url]
'Coded By:  raskew
'Inputs:    from debug window:
'           ? alphanum("blackwhite", 21817)
'Output:    LBIBH
'*******************************************

Dim strOut  As String
Dim strHold As String
Dim intHold As String
Dim n       As Integer

    If Direction = "Char" Then
        strHold = pCode
        For n = 1 To Len(pNum)
           intHold = Mid(pNum, n, 1)
           strOut = strOut & Mid(strHold, IIf(intHold = 0, 10, intHold), 1)
        Next n
        
        AlphaNum = UCase(strOut)
    Else
        strHold = pCode
        For n = 1 To Len(pNum)
           intHold = Mid(pNum, n, 1)
           strOut = strOut & InStr(pCode, intHold)
        Next n
        AlphaNum = (strOut)
    End If


End Function

 

[raskew]

Thanks for that. It

works like a charm!

Bob

 

[theserginhus]

Put It To Work

PDX MAN,
I tried your code many times and it says "compile Error - Argument not Optional". When I use Raskew's code, it works fine! What is wrong? Also, if I want to call this function when I enter numbers in a especific FIELD, how'd I do that? Say I enter 555 on AMT and I want to get III on field CODE, how would it work?

Thanks,

And to enhance this working code, I added the ability to go from code to #s.

Function AlphaNum(pCode As String, pNum As Variant, Direction As String) As String
'*******************************************
'Re:        [url]http://www.access-programmers.co.uk...3918#post283918[/url]
'Coded By:  raskew
'Inputs:    from debug window:
'           ? alphanum("blackwhite", 21817)
'Output:    LBIBH
'*******************************************

Dim strOut  As String
Dim strHold As String
Dim intHold As String
Dim n       As Integer

    If Direction = "Char" Then
        strHold = pCode
        For n = 1 To Len(pNum)
           intHold = Mid(pNum, n, 1)
           strOut = strOut & Mid(strHold, IIf(intHold = 0, 10, intHold), 1)
        Next n
        
        AlphaNum = UCase(strOut)
    Else
        strHold = pCode
        For n = 1 To Len(pNum)
           intHold = Mid(pNum, n, 1)
           strOut = strOut & InStr(pCode, intHold)
        Next n
        AlphaNum = (strOut)
    End If


End Function

 

[pdx_man]

You must include the direction that you would like to go as the third argument. If you want to return Character data ie LBIBH, then you would have:
alphanum("blackwhite", 21817,"Char")

If you want Numeric data returned, then

alphanum("blackwhite", "LBIBH", "Numeric")

You could have the last argument be optional:

Function AlphaNum(pCode As String, pNum As Variant, Optional Direction As String) As String

If IsMissing(Direction) Then Direction = "Char"

If Direction = "Char" Then
strHold = pCode
:
:

Then, if you exclude the third argument from your call, it will assume you have a number and want to return the characters.

Of course, you could use the IsNumeric Function to test the input data and assume you want the opposite ...

Function AlphaNum(pCode As String, pNum As Variant) As String

If IsNumeric(pNum) Then
strHold = pCode
:
:


oh, change this line in my bottom part of code:

strOut = strOut & InStr(pCode, intHold)

to

strOut = strOut & InStr(pCode, intHold) Mod 10

see why?

 

[pdx_man]

To implement this on a form to display what you enter into a text box, we'll name it TheValue, have another text box that is not enabled have this in the Control Source:

=alphanum("blackwhite", [TheValue])

Play with that.

 

[theserginhus]

thanks

Thanks PDX-MAN, you helped a lot. Have a great weekend!!

To implement this on a form to display what you enter into a text box, we'll name it TheValue, have another text box that is not enabled have this in the Control Source:

=alphanum("blackwhite", [TheValue])

Play with that.