MS Access data conversion - convert currency to letters

Author

convert currency to letters

snail

2005-11-26, 11:23 am

I have a cost field in table to let me know the cost of the product. Using
the word "authorizes" would like to convert the currency to coinciding
letter of "authorizes". 1=a, 2=u, 3=t, 4=h, 5=o, 6=r, 7=i, 8=z, 9=e and
0=s. Would like this to work automatically in a field picking up the
information from my cost field. The decimal point would not matter because
I would know that the last 2 letters were the cents. Want this so that I
can list my cost next to the selling price on my price list and nobody
besides me would know what my cost is. This way I can figure a new selling
price for quantity orders. Your help would be greatly appreciated.

John Nurick

2005-11-26, 1:23 pm

This function should do the job, used in a calculated field in a query
or a textbox on a form or report:

Function EncodeCost(Cost As Variant) As Variant
Const CodeWord = "SAUTHORIZE" '0 comes before 1, not after 9
Dim S As String
Dim j As Long

'Value must be numeric
If Not IsNumeric(Cost) Then Exit Function

'Convert to string of digits, omitting decimal point
S = Format(Cost * 100, "0")
For j = 1 To Len(S)
'Replace each character in S
Mid(S, j, 1) = Mid(CodeWord, CInt(Mid(S, j, 1)) + 1, 1)
Next
EncodeCost = S
End Function

On Sat, 26 Nov 2005 17:22:42 GMT, "snail" <bouper@infionline.net> wrote:

>
>I have a cost field in table to let me know the cost of the product. Using
>the word "authorizes" would like to convert the currency to coinciding
>letter of "authorizes". 1=a, 2=u, 3=t, 4=h, 5=o, 6=r, 7=i, 8=z, 9=e and
>0=s. Would like this to work automatically in a field picking up the
>information from my cost field. The decimal point would not matter because
>I would know that the last 2 letters were the cents. Want this so that I
>can list my cost next to the selling price on my price list and nobody
>besides me would know what my cost is. This way I can figure a new selling
>price for quantity orders. Your help would be greatly appreciated.
>
>

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

Brendan Reynolds

2005-11-26, 1:23 pm

Public Function Obscure(ByVal curTheNumber As Currency) As String

Const strcTheText As String = "authorizes"
Dim strSource As String
Dim lngLoop As Long
Dim strTarget As String

strSource = CStr(Int(curTheNumbe
r * 100))
For lngLoop = 1 To Len(strSource)
If Mid$(strSource, lngLoop, 1) = "0" Then
strTarget = strTarget & Right$(strcTheText, 1)
Else
strTarget = strTarget & Mid$(strcTheText, CInt(Mid$(strSource,

lngLoop, 1)), 1)
End If
Next lngLoop

Obscure = strTarget

End Function

Example of use, in Immediate window ...

? obscure(12345678.90)
authorizes

--
Brendan Reynolds

"snail" <bouper@infionline.net> wrote in message
news:CR0if.5547$N45.5160@newsread1.news.atl.earthlink.net...
>I have a cost field in table to let me know the cost of the product. Using
>the word "authorizes" would like to convert the currency to coinciding
>letter of "authorizes". 1=a, 2=u, 3=t, 4=h, 5=o, 6=r, 7=i, 8=z, 9=e and
>0=s. Would like this to work automatically in a field picking up the
>information from my cost field. The decimal point would not matter because
>I would know that the last 2 letters were the cents. Want this so that I
>can list my cost next to the selling price on my price list and nobody
>besides me would know what my cost is. This way I can figure a new selling
>price for quantity orders. Your help would be greatly appreciated.
>

Sponsored Links