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
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