Convert overpunch characters to currency

T

Tracey

I have received a text file where currency is indicated in a format called
overpunch characters. An example is "00000015{" (without the quotation marks)
which needs to translate to $1.50. The last character determines both the
last digit of the currency sequence as well as the sign (positive or
negative) of the currency transaction. Another example would be "00000019M"
which would translate to negative $1.94. There are 20 possible characters for
this last digit. I need to run an update query which would change all these
codes into the correct currency. Within the dataset there are a total of 24
columns containing this type of code.

I have no clue how to begin doing this. I am very weak in writing code and
would appreciate a sample as to how to begin this process. Any help would be
greatly appreciated!
 
K

KARL DEWEY

It seems that the Overpunch character is a multiplier.
Can you post a translation table with two fields - Overpunch and Function?
 
K

KARL DEWEY

Use the table below named [OverP data] and this query --
SELECT [OverP data].AA, [OverP data].DD, IIf([Sign]="+",Val(Left([DD],8) &
IIf(Right([DD],1)=[Char],[Digit],Null))*0.01,Val(Left([DD],8) &
IIf(Right([DD],1)=[Char],[Digit],Null))*-0.01) AS Expr1, Len(Left([DD],8) &
IIf(Right([DD],1)=[Char],[Digit],Null)) AS Expr2
FROM OverPunch, [OverP data]
WHERE (((Len(Left([DD],8) & IIf(Right([DD],1)=[Char],[Digit],Null)))=9));

Digit Char Sign
0 } -
1 J -
2 K -
3 L -
4 M -
5 N -
6 O -
7 P -
8 Q -
9 R -
0 { +
1 A +
2 B +
3 C +
4 D +
5 E +
6 F +
7 G +
8 H +
9 I +
 
T

Tracey

Thanks for the assistance. I'm pretty new at this so let me work with the
instructions below and see if I can make it work. I'm probably going to have
to come back with some more questions.

KARL DEWEY said:
Use the table below named [OverP data] and this query --
SELECT [OverP data].AA, [OverP data].DD, IIf([Sign]="+",Val(Left([DD],8) &
IIf(Right([DD],1)=[Char],[Digit],Null))*0.01,Val(Left([DD],8) &
IIf(Right([DD],1)=[Char],[Digit],Null))*-0.01) AS Expr1, Len(Left([DD],8) &
IIf(Right([DD],1)=[Char],[Digit],Null)) AS Expr2
FROM OverPunch, [OverP data]
WHERE (((Len(Left([DD],8) & IIf(Right([DD],1)=[Char],[Digit],Null)))=9));

Digit Char Sign
0 } -
1 J -
2 K -
3 L -
4 M -
5 N -
6 O -
7 P -
8 Q -
9 R -
0 { +
1 A +
2 B +
3 C +
4 D +
5 E +
6 F +
7 G +
8 H +
9 I +

Tracey said:
I have received a text file where currency is indicated in a format called
overpunch characters. An example is "00000015{" (without the quotation marks)
which needs to translate to $1.50. The last character determines both the
last digit of the currency sequence as well as the sign (positive or
negative) of the currency transaction. Another example would be "00000019M"
which would translate to negative $1.94. There are 20 possible characters for
this last digit. I need to run an update query which would change all these
codes into the correct currency. Within the dataset there are a total of 24
columns containing this type of code.

I have no clue how to begin doing this. I am very weak in writing code and
would appreciate a sample as to how to begin this process. Any help would be
greatly appreciated!
Click to expand...
Click to expand...
 
Top