# Convert overpunch characters to currency

G

#### Guest

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!

G

#### Guest

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

G

#### Guest

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 +

J

#### Jamie Collins

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

can I ask why you chose to cast the 'Digit' value as double float
rather than an integer?

I've noticed over the last couple of days that some of the regulars in
the Access groups use Val which, given the nature of double floating
point values, would seem an odd choice when more explicit casting
functions are available.

TIA,
Jamie.

--

G

#### Guest

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!

#### Violet

the query looks very good however I'm not sure what the "AA" and "DD" represent. Is DD the field name from the table that holds the characters to convert? In my case [Jan2015_Begin].amtPaid? And what is AA? Thank you in advance for the clarification.