S9(9)V99, convert to currency 1

[HEMASUR]

I have a currency field in the above format, the last digit is an alpha character, that indicates a negative number. I do not know how to write sql statements, but if someone can help me with a simple replace query, where the last digit can be replaced by a digit that would be of great help.
Thanks
Hema

 

[SkipVought]

Hema,

It's not quite that simple.

1. the decimal point is implied. That's not a problem.
2. the last character represents BOTH the least significant digit and the sign.
[tt]
{ 0
A 1
B 2
C 3
D 4
E 5
F 6
G 7
H 8
I 9

} -0
J -1
K -2
L -3
M -4
N -5
O -6
P -7
Q -8
R -9
[/tt]
Use MID or RIGHT to work on the last character and decode.

Multiply the LEFT part by 10, add the Right part and then divide the result by 100, the implied decimal.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Or better yet, have a COBOL programmer, convert the packed decimal to decimal, in the flat file.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[HEMASUR]

Thanks Skip,
Luckily the last digits are only used for neagtive numbers, so that saves me a few steps. I started out with the suggestion that you have given, and thought may be there could be an easier solution. I fould a cobol solution on-line, but have no idea on how to adapt it to access.

http://www.tek-tips.com/viewthread.cfm?qid=1474340&page=1

thread388-1474340.
I am thinking of breaking the column into two and then using a table where the characters are replaced with the negative numbers, then combining the two columns back to one. I am hoping that it will work.
Thanks once again.

Hema

 

[SkipVought]

Please post some sample data from your input file.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[HEMASUR]

Hello Skip,
Here is some sample data.
Thanks
Hema

IngrCstPd DispFeePd TotAmtPd BillAmt
00000038M 00000061O 000000000 00000174R
00000005P 00000094L 000000000 00000109R
00000037Q 00000062K 000000000 00000135R
00000034O 00000017N 00000002J 00000132R
00000048R 00000017N 00000016M 00000126R
00000053P 00000017N 00000021K 00000150R
00000042N 00000107N 000000000 00000275R
00000040} 000000000 000000000 00000040}

 

[SkipVought]

example
[tt]
SELECT IngrCstPd,
iif(RIGHT(IngrCstPd,1)='{',1,iif(RIGHT(IngrCstPd,1)='}',-1,iif(RIGHT(IngrCstPd,1)<'J',1,-1)))*
(Left(IngrCstPd,LEN(IngrCstPd)-1)*10 +
IIF(RIGHT(IngrCstPd,1)='{' or RIGHT(IngrCstPd,1)='}',0, iif(RIGHT(IngrCstPd,1)<'J',ASC(RIGHT(IngrCstPd,1))-64,iif(RIGHT(IngrCstPd,1)<'S',ASC(RIGHT(IngrCstPd,1))-73,0))))

FROM [COBOL_File]
[/tt]

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

I forgot to add the operation for the implied decimal...
[tt]
SELECT IngrCstPd,
iif(RIGHT(IngrCstPd,1)='{',1,iif(RIGHT(IngrCstPd,1)='}',-1,iif(RIGHT(IngrCstPd,1)<'J',1,-1)))*
(Left(IngrCstPd,LEN(IngrCstPd)-1)*10 +
IIF(RIGHT(IngrCstPd,1)='{' or RIGHT(IngrCstPd,1)='}',0, iif(RIGHT(IngrCstPd,1)<'J',ASC(RIGHT(IngrCstPd,1))-64,iif(RIGHT(IngrCstPd,1)<'S',ASC(RIGHT(IngrCstPd,1))-73,0))))/100

FROM [COBOL_File]
[/tt]



Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[HEMASUR]

Hello Skip,

Thank you sooooooooooooo much
It works beautifully!!!

Hema