Convert overpunch characters to currency

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Tracey - 30 May 2006 20:35 GMT

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!

Reply to this Message

KARL DEWEY - 30 May 2006 20:58 GMT

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

> I have received a text file where currency is indicated in a format called
> overpunch characters. An example is "00000015{" (without the quotation marks)
[quoted text clipped - 9 lines]
> would appreciate a sample as to how to begin this process. Any help would be
> greatly appreciated!

Reply to this Message

KARL DEWEY - 30 May 2006 21:28 GMT

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    +

Reply to this Message

Jamie Collins - 31 May 2006 12:21 GMT

> Use the table below named [OverP data] and this query --
> SELECT [OverP data].AA, [OverP data].DD, IIf([Sign]="+",Val(Left([DD],8) &
[quoted text clipped - 25 lines]
> 8 H +
> 9 I +

With reference to another thread
(http://groups.google.com/group/microsoft.public.access/browse_frm/thread/539085b
8b736d6d9/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.

--

Reply to this Message

Tracey - 31 May 2006 16:35 GMT

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) &
[quoted text clipped - 39 lines]
> > would appreciate a sample as to how to begin this process. Any help would be
> > greatly appreciated!

Reply to this Message