ASCII Convert

[Sam]

I have sample values listed below in a field called AMT1.
002527}
0024763
002349Q
002052}
0019258
001645K
001237B
004567J


and I need to convert these values to the true number as exampled
below:

As an example the data below will convert a signed numeric value for a
specific field.

 Sample AMT1 = 1237B
This is amount would need to be converted as 123.72

 2nd Sample AMT = 4567J
This is amount would need to be converted as 456.71- or (456.71)

I have a chart of the ascii character to the number value.

What would be the easiest way to convert these values?

Thanks for your help!!!!

 

[Jeff Boyce]

Sam

I'm not seeing the relationship ...

How does "B" become "2", but "J" become "1" (and cause the amount to become
negative)? Do you have a table of the conversions?

More info, please...

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.


I have sample values listed below in a field called AMT1.
002527}
0024763
002349Q
002052}
0019258
001645K
001237B
004567J


and I need to convert these values to the true number as exampled
below:

As an example the data below will convert a signed numeric value for a
specific field.

? Sample AMT1 = 1237B
This is amount would need to be converted as 123.72

? 2nd Sample AMT = 4567J
This is amount would need to be converted as 456.71- or (456.71)

I have a chart of the ascii character to the number value.

What would be the easiest way to convert these values?

Thanks for your help!!!!

 

[Dirk Goldgar]

A long time ago I wrote this function in response to a similar post:

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

In your case, you would pass the value 2 for the DecimalPlaces argument:

?fncZonedToNumber("1237B", 2)
123.72
?fncZonedToNumber("4567J", 2)
-456.71

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)

I have sample values listed below in a field called AMT1.
002527}
0024763
002349Q
002052}
0019258
001645K
001237B
004567J


and I need to convert these values to the true number as exampled
below:

As an example the data below will convert a signed numeric value for a
specific field.

 Sample AMT1 = 1237B
This is amount would need to be converted as 123.72

 2nd Sample AMT = 4567J
This is amount would need to be converted as 456.71- or (456.71)

I have a chart of the ascii character to the number value.

What would be the easiest way to convert these values?

Thanks for your help!!!!

 

[Sam]

Thanks for your response, I have a table called ASCII SIGNED NUMERIC
FIELDS CONVERSION VALUES AND FORMULAS and the relationship is listed
below
Value Character

+0 {
+1 A
+2 B
+3 C
+4 D
+5 E
+6 F
+7 G
+8 H
+9 I

-0 }
-1 J
-2 K
-3 L
-4 M
-5 N
-6 O
-7 P
-8 Q
-9 R

 

[KARL DEWEY]

You did not post your conversion table so this does not accomadate the '}'
you have.
This would be shorter using your table.

Expr1: IIf(Right([AMT1],1) Between "J" And
"S",-1,1)*(Val([AMT1])/IIf(IsNumeric([AMT1]),100,10)+(IIf(Right([AMT1],1)
Between "A" And "I",(Asc(Right([AMT1],1))-64)/100,0))+(IIf(Right([AMT1],1)
Between "J" And "S",(Asc(Right([AMT1],1))-73)/100,0)))

 

[Sam]

Dirk, this worked amazingly. THANKS SO MUCH

A long time ago I wrote this function in response to a similar post:

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

In your case, you would pass the value 2 for the DecimalPlaces argument:

    ?fncZonedToNumber("1237B", 2)
     123.72
    ?fncZonedToNumber("4567J", 2)
    -456.71

--
Dirk Goldgar, MS Access MVP
Access tips:www.datagnostics.com/tips.html

(please reply to the newsgroup)


I have sample values listed below in a field called AMT1.
002527}
0024763
002349Q
002052}
0019258
001645K
001237B
004567J

and I need to convert these values to the true number as exampled
below:

As an example the data below will convert a signed numeric value for a
specific field.

 Sample AMT1 = 1237B
This is amount would need to be converted as 123.72

 2nd Sample AMT = 4567J
This is amount would need to be converted as 456.71- or (456.71)

I have a chart of the ascii character to the number value.

What would be the easiest way to convert these values?

Thanks for your help!!!!

 

[KARL DEWEY]

Jeff,
If I remember this is an old number scheme used in a very old computer
system. I think the character series the '}' is in changes the decimal
position.

 

[Jeff Boyce]

Hmph! And I thought I was old ...<g>.

Jeff B.

Jeff,
If I remember this is an old number scheme used in a very old computer
system. I think the character series the '}' is in changes the decimal
position.

 

[Dirk Goldgar]

If I remember this is an old number scheme used in a very old computer
system. I think the character series the '}' is in changes the decimal
position.

It's called Zoned Decimal, and it was used with the EBCDIC coding scheme.
In that scheme, the characters '0' to '9' were represented by hexadecimal
values F0 to F9. In Zoned Decimal coding, the digit in the last character
position of a signed number represented as text had its high-order four bits
overwritten with values other than F to indicate whether the number's value
was positive or negative.

C (last digit C0 through C9) = positive
D (last digit D0 through D9) = negative
F (last digit F0 through F9) = unsigned, assumed positive

Ah, it takes me back ...

 

[Jeff Boyce]

I understand the "how", now, but why?!

Sure seems like a lot of work just to add a minus sign...

Skippy

 

[KARL DEWEY]

You might read this --
http://www.simotime.com/datazd01.htm
Zoned Decimal Format - Description and Discussion

This document will focus on a discussion of a numeric field (or data string)
format referred to as zoned decimal and is commonly used on an IBM Mainframe
System. This is the default numeric format for COBOL and may be explicitly
defined with the "USAGE IS DISPLAY" clause

Note: The items in this document are appropriate for applications that are
written in COBOL, Mainframe Assembler (HLASM) or PL/I. The IBM Mainframe
architecture drove many of the numeric formats that existed in the early ANSI
specifications for COBOL.and have been carried forward to the current COBOL
ANSI specifications.

The "SIGNED, ZONED-DECIMAL" format has caused problems for years for
mainframe programmers because the signed position of the field will display
as a character value of "A-I" for positive numbers and "J-R" for negative
numbers. It also allows for a positive and negative zero value that will
display as a "{" and "}", this is the curly brace symbol or x'C0' and x'D0'.

When converting this type of field from EBCDIC to ASCII the programmer is
presented with a number of confusing options and challenges. This document
will try to explain the options and help the programmer avoid the common
mistakes that are made during a conversion process.

 

[Jeff Boyce]

Thanks for the reference, Karl.

It sounds like this format is a carry-over from dealing with the limitations
of the punch card.

Sorta like the story of why we cut the end off the ham before putting it
into the pan...<g>

Regards

Jeff B.