Convert overpunch characters to currency

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

 

[Guest]

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

 

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

 

[Jamie Collins]

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 +

With reference to another thread
(http://groups.google.com/group/micr..._frm/thread/539085b8b736d6d9/fe60b65955b00e66),
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.

--

 

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

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 +

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.