An elegant text-to-number conversion ? (1 Viewer)

[indyaries]

Greetings,

Wondering if there is a more "elegant" way to perform a task than the one I'm using.

I'm working with text numbers. The field size [Amount] is 10. But, here's the catch;

These text numbers use a letter or special character for the last place in the field. This letter or character not only defines what the last "number" is, but also if it's positive or negative.

Here is an example;
POSITIVE NEGATIVE
{ 0 }
A 1 J
B 2 K
C 3 L
D 4 M
E 5 N
F 6 O
G 7 P
H 8 Q
I 9 R

In my query, I have a field called Amount which contains the text amount (ex:000002438G = $243.87, while
000000300L = -$30.03)

I also have a field I create in the query called Conv_Amt. The expression below is how this looks in the Query grid, and it's all on one line:

Conv_Amt: Sgn(11-InStr("{ABCDEFGHI=}JKLMNOPQR",Right([amount],1)))*Val(Left([amount],Len([amount])-1) & Right(Str(InStr("{ABCDEFGHI}JKLMNOPQR",Right([amount],1))-1),1))/"100"

Again, was wondering if there was a more elegant way to convert these text numbers into regular numbers other than the expression I'm using above.

Thanks,

Bob in Indy

 

[pcs]

bob in indy,

in my book, you have already achieved 'elegance'

i assume this is imported data.

i just have to ask what is the data source?

al

 

[Pat Hartman]

Unless you can get your mainframe programmers to modify the extract so that the field is exported as formatted, you've found a good solution. The programmer would need to change the picture clause of the field to
9(9).99-
and you would have to change the field length to 13 to allow for this. That picture clause would give you 9 significant digits, a decimal point, 2 decimal digits and a minus sign if the number is negative otherwise a trailling space.

 

[indyaries]

Hmmm....well, I doubt I'll ever get the programmers to change anything.

I'm working with the Dept of Defense...ergo, any suggestions I make now might be considered by 2025 or so. Besides, why enhance the mainframe when management can have us jump through hoops for workarounds.

Many thanks to both of you for replying.

Bob in Indy

 

[pcs]

bob in indy,

i had not seen a pic like that, i defer to Pat

just consider 2025 as your retirement date, until then it is 'job security'

al

 

[raskew]

Have you given thought to a little UDF, i.e.:

? widcon("000002438G")
243.87

? widcon("000000300L")
-30.03

with widcon() being: (Revised)

Function WidCon(wid As String) As Currency
Dim widdiv, widplus, ascx

ascx = Asc(Right(wid, 1))
widdiv = Switch(ascx < 74 Or ascx = 123, 100, True, -100)

widplus = Switch(ascx < 74, 64, ascx < 123, 73, True, ascx)

WidCon = (10 * Val(Left(wid, 9)) + (ascx - widplus)) / widdiv

End Function