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" <(E-Mail Removed)> wrote in message
news:B402EA21-AD12-4AE5-B9E2-(E-Mail Removed)...
> 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?


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 Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

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

"Dirk Goldgar" wrote:

> "cg" <(E-Mail Removed)> wrote in message
> news:B402EA21-AD12-4AE5-B9E2-(E-Mail Removed)...
> > 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?
>
>
> 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 Goldgar, MS Access MVP
> www.datagnostics.com
>
> (please reply to the newsgroup)
>

"cg" <(E-Mail Removed)> wrote in message
news:1FA65F1F-A06F-4F25-AFC9-(E-Mail Removed)...
> Dirk,
>
> Worked like a charm - THANKS A MILLION!

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


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


--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)