Zoned Decimal to Signed Access Number?

[MikeV06]

I have a flat file that has zoned a zoned decimal number 0000005144E where
the last character indicates its value and sign. In this case the "E" needs
to be changed to a "5" and the result left as positive. If the sign digit
is a "K" then it would be changed to a "2" and the result multiplied by a
-1. The file is linked and I wanted to incorporate a conversion in a make
table query.

I can do this with a Select Case/Case VB script but am at a loss as to how
I would do it with Access. Would someone please get me pointed in the
correct direction.

Thanks, Mike.


011010Cash in Bank - Yr 1 0000005144E

Value Positive Negative
0 { }
1 A J
2 B K
3 C L
4 D M
5 E N
6 F O
7 G P
8 H Q
9 I R

 

[John Nurick]

Hi Mike,

It's probably simplest to use a VBA function in a calculated field in
the query. If you search groups.google.com for
fncZonedToNumber
you'll find at least one example.

 

[MikeV06]

Thank you. I was not quite sure how to use a VBA function with expression
builder. I guess I could nest 20 IIfs together if all else fails. I will go
search for the fnc... now.