Converting a Signed Overpunch value to a number

  • Thread starter Thread starter cg
  • Start date Start date
C

cg

All,

Ok, this is definitely one that has me stumped. I am pulling data into my
Access Database from a database that was built quite some time ago. For some
reason, several of the sales fields are displaying in Signed Overpunch. For
example, where you normally see $1,529.36 the field actually stated
00000015293F.

Anyone know of a quick way to convert this to a number?

Thanks!!!!
 
cg said:
All,

Ok, this is definitely one that has me stumped. I am pulling data into my
Access Database from a database that was built quite some time ago. For
some
reason, several of the sales fields are displaying in Signed Overpunch.
For
example, where you normally see $1,529.36 the field actually stated
00000015293F.

Anyone know of a quick way to convert this to a number?
Click to expand...


Seems to me I worked this out once, quite a long time ago. Let's see ...
searching ... ah, here it is:

'----- start of code -----
Function fncZonedToNumber(ZonedValue As Variant) As Variant

Dim strValue As String
Dim strLast As String

If IsNull(ZonedValue) Then
fncZonedToNumber = Null
ElseIf VarType(ZonedValue) <> vbString Then
fncZonedToNumber = CVErr(5) ' invalid argument
ElseIf Len(ZonedValue) = 0 Then
fncZonedToNumber = Null
Else
strLast = Right(ZonedValue, 1)
strValue = Left(ZonedValue, Len(ZonedValue) - 1)

If InStr(1, "0123456789", strLast, vbBinaryCompare) Then
strValue = strValue & strLast
ElseIf InStr(1, "ABCDEFGHI", strLast, vbBinaryCompare) Then
strValue = strValue & Chr(Asc(strLast) - 16)
ElseIf InStr(1, "JKLMNOPQR", strLast, vbBinaryCompare) Then
strValue = "-" & strValue & Chr(Asc(strLast) - 25)
ElseIf StrComp(strLast, "{", vbBinaryCompare) = 0 Then
strValue = strValue & "0"
ElseIf StrComp(strLast, "}", vbBinaryCompare) = 0 Then
strValue = "-" & strValue & "0"
Else
fncZonedToNumber = CVErr(5) ' invalid argument
Exit Function
End If

fncZonedToNumber = Val(strValue)
End If

End Function

'----- end of code -----

Note that the above function returns a Double value, and knows nothing about
any implied decimal points.
 
Dirk,

Worked like a charm - THANKS A MILLION!

Can you tell me how to input a decimal? It appears that the decimal comes
before the last two digits, so:

00000015293F should be $1,539.36

Thanks!!!!
 
cg said:
Dirk,

Worked like a charm - THANKS A MILLION!
Click to expand...

You're welcome. I had written that function to answer a newsgroup question
years ago. Way back in my mainframe-programming days I had to deal with
zoned decimal numbers fairly often.
Can you tell me how to input a decimal? It appears that the decimal comes
before the last two digits, so:

00000015293F should be $1,539.36
Click to expand...


How about this untested modification of the original function, which lets
you pass the number implied of decimal places?

'----- start of code -----
Function fncZonedToNumber( _
ZonedValue As Variant, _
Optional DecimalPlaces As Integer = 0) _
As Variant

Dim strValue As String
Dim strLast As String

If IsNull(ZonedValue) Then
fncZonedToNumber = Null
ElseIf VarType(ZonedValue) <> vbString Then
fncZonedToNumber = CVErr(5) ' invalid argument
ElseIf Len(ZonedValue) = 0 Then
fncZonedToNumber = Null
Else
strLast = Right(ZonedValue, 1)
strValue = Left(ZonedValue, Len(ZonedValue) - 1)

If InStr(1, "0123456789", strLast, vbBinaryCompare) Then
strValue = strValue & strLast
ElseIf InStr(1, "ABCDEFGHI", strLast, vbBinaryCompare) Then
strValue = strValue & Chr(Asc(strLast) - 16)
ElseIf InStr(1, "JKLMNOPQR", strLast, vbBinaryCompare) Then
strValue = "-" & strValue & Chr(Asc(strLast) - 25)
ElseIf StrComp(strLast, "{", vbBinaryCompare) = 0 Then
strValue = strValue & "0"
ElseIf StrComp(strLast, "}", vbBinaryCompare) = 0 Then
strValue = "-" & strValue & "0"
Else
fncZonedToNumber = CVErr(5) ' invalid argument
Exit Function
End If

If DecimalPlaces = 0 Then
fncZonedToNumber = Val(strValue)
Else
fncZonedToNumber = Val(strValue) / (10 ^ DecimalPlaces)
End If

End If

End Function
'----- end of code -----

Testing in the Immediate window:

?fncZonedToNumber("00000015293F", 2)
1529.36
 
Back
Top