MSGROUPS.NET | Search | Post Question | Groups | Stream | About | Register

### convert currency to letters

• Follow

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.

 0

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.

 0

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(curTheNumber * 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
>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.
>

 0