<?php
// Some examples
echo NumberConverter::signedOverpunch2Numeric("6705N") . "\n";
echo NumberConverter::signedOverpunch2Numeric("77}") . "\n";
echo NumberConverter::signedOverpunch2Numeric("12A") . "\n";
// The class
class NumberConverter
{
public static $sopTable = array(
"}" => array(0,-1),
"J" => array(1,-1),
"K" => array(2,-1),
"L" => array(3,-1),
"M" => array(4,-1),
"N" => array(5,-1),
"O" => array(6,-1),
"P" => array(7,-1),
"Q" => array(8,-1),
"R" => array(9,-1),
"{" => array(0,1),
"A" => array(1,1),
"B" => array(2,1),
"C" => array(3,1),
"D" => array(4,1),
"E" => array(5,1),
"F" => array(6,1),
"G" => array(7,1),
"H" => array(8,1),
"I" => array(9,1)
);
public static function signedOverpunch2Numeric($in)
{
$code = substr($in,-1);
$val = $in;
if(isset(self::$sopTable[$code]))
{
$digit = self::$sopTable[$code][0];
$sign = self::$sopTable[$code][1];
$val = (substr($in,0,-1) . $digit) * $sign;
}
return ($val / 100);
}
}
Public Function SignedNum(theAmt As Variant) As Double
Dim theLast As String
Dim theFrnt As String
Dim isNegative As Boolean
theLast = UCase(Right(Trim(theAmt), 1))
theFrnt = Left(theAmt, Len(theAmt) - 1)
Select Case theLast
Case "{"
theLast = "0"
Case "A" To "I"
theLast = Asc(theLast) - 64
Case "}"
theLast = "0"
isNegative = True
Case "J" To "R"
theLast = Asc(theLast) - 73
isNegative = True
End Select
SignedNum = Val(theFrnt & theLast) / 100 * IIf(isNegative, -1, 1)
End Function
Just add the code above to a module. And to update your table, just run an Update Query to update the field. The SQL for your query should look like this:Update NameOfTable Set [Balance] = SignedNum([Balance])
Ron
Join the community of 500,000 technology professionals and ask your questions.
Connect with top rated Experts
11 Experts available now in Live!