Replacing Character and omission of decimal 2

[sqladmin99]

Hi All,

What would be the best way to replace numerical digit with alphabetical character at the same time omitting any decimal point in the Number?

Here is what I am trying to do.

We have one table with pricing information.

Item # Item Price

1 3.44
2 4.45
3 23.99
4 1.02

We want to convert item price to character field. Rules for conversion is

if digit is 0 than o
if digit is 1 than -
if digit is 2 than )
if digit is 3 than L
if digit is 4 than H
if digit is 5 than u
if digit is 6 than z and so on...

We also want to omit decimal from the number. For example, if price is 2.45 than new value after conversion would be )Hu.

I will appreciate your suggestions.

Thank you,

Raj

 

[scriverb]

Well first of all I would need the entire conversion table. It is not openly apparent as to what comes after 6. So, provide that and I will provide you with a function and query to perform the conversion.

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[mpastore]

Paste the following into a module (you will need to add additional numbers to array):

Function convert_num(pvNum As Variant) As String
' ?convert_num(2.45 ) ' yields )Hu
Const kNum = 1
Const kChar = 2

ReDim asConvert(10, 2) As String
asConvert(0, kNum) = "0"
asConvert(0, kChar) = "o"
asConvert(1, kNum) = "1"
asConvert(1, kChar) = "-"
asConvert(2, kNum) = "2"
asConvert(2, kChar) = &quot"
asConvert(3, kNum) = "3"
asConvert(3, kChar) = "L"
asConvert(4, kNum) = "4"
asConvert(4, kChar) = "H"
asConvert(5, kNum) = "5"
asConvert(5, kChar) = "u"
asConvert(6, kNum) = "6"
asConvert(6, kChar) = "u"

Dim sNum As String
sNum = CStr(pvNum)
sNum = Replace(sNum, ".", "&quot

Dim iCntr As Integer
For iCntr = 0 To UBound(asConvert)
sNum = Replace(sNum, asConvert(iCntr, kNum), asConvert(iCntr, kChar))
Next iCntr

convert_num = sNum
End Function


Mike Pastore

Hats off to (Roy) Harper

 

[sqladmin99]

Thanks a lot 'mpastore'. It worked just fine. I really appreciate your help. You definitely get star for this.

Raj