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

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.


"Sam" <(E-Mail Removed)> wrote in message
news:34bac5c7-3746-4039-84e5-(E-Mail Removed)...
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!!!!

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)

"Sam" <(E-Mail Removed)> wrote in message
news:34bac5c7-3746-4039-84e5-(E-Mail Removed)...
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!!!!

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


On Dec 22, 12:43*pm, "Jeff Boyce" <nonse...@nonsense.com> wrote:
> 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.
>
> "Sam" <samcan...@gmail.com> wrote in message
>
> news:34bac5c7-3746-4039-84e5-(E-Mail Removed)...
> 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!!!!

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

--
Build a little, test a little.


"Sam" wrote:

> 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, this worked amazingly. THANKS SO MUCH


On Dec 22, 12:45Â*pm, "Dirk Goldgar"
<d...@NOdataSPAMgnostics.com.invalid> wrote:
> 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)
>
> "Sam" <samcan...@gmail.com> wrote in message
>
> news:34bac5c7-3746-4039-84e5-(E-Mail Removed)...
> 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,
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.

--
Build a little, test a little.


"Jeff Boyce" wrote:

> 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.
>
>
> "Sam" <(E-Mail Removed)> wrote in message
> news:34bac5c7-3746-4039-84e5-(E-Mail Removed)...
> 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!!!!
>
>
>
> .
>

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

Jeff B.

"KARL DEWEY" <(E-Mail Removed)> wrote in message
news:35E05B6D-AE91-4D72-80A4-(E-Mail Removed)...
> 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.
>
> --
> Build a little, test a little.
>
>
> "Jeff Boyce" wrote:
>
>> 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.
>>
>>
>> "Sam" <(E-Mail Removed)> wrote in message
>> news:34bac5c7-3746-4039-84e5-(E-Mail Removed)...
>> 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" <(E-Mail Removed)> wrote in message
news:35E05B6D-AE91-4D72-80A4-(E-Mail Removed)...
> 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 ...

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

(please reply to the newsgroup)

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

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

Skippy

"Dirk Goldgar" <(E-Mail Removed)> wrote in message
news:1A7D9D59-01B9-464F-9DDD-(E-Mail Removed)...
> "KARL DEWEY" <(E-Mail Removed)> wrote in message
> news:35E05B6D-AE91-4D72-80A4-(E-Mail Removed)...
>> 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 ...
>
> --
> Dirk Goldgar, MS Access MVP
> Access tips: www.datagnostics.com/tips.html
>
> (please reply to the newsgroup)
>