Converting Sined Characters into numbers.

[Guest]

I have several files I've imported from txt files and they all contain fields
that the last character is a sine character({-I) where {=0, A=1,B=2,etc,etc.
For example 0001A = 11. I have to run queriers involving those sined fields
but I have to convert them to real numbers first. Is there a macro or some
code I can use to automate since I'm deal with several different files
containing over 200k records? Thank you in advance for help you can give me.

 

[Dirk Goldgar]

I have several files I've imported from txt files and they all
contain fields that the last character is a sine character({-I) where
{=0, A=1,B=2,etc,etc. For example 0001A = 11. I have to run queriers
involving those sined fields but I have to convert them to real
numbers first. Is there a macro or some code I can use to automate
since I'm deal with several different files containing over 200k
records? Thank you in advance for help you can give me.

I'm thinking that you are dealing with values in an ASCII translation of
the zoned decimal format. If so, the translation table for the last
digit, which has the sign nybble, is this:

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

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

Here's a function that, I think, will make the translation for you.

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

 

[Guest]

Thank you very much. I'm sure this is exactly what I need but being I'm still
kinda of new to access, I must be missing where I actually direct the code
towards the particular table and field that I need to convert. I only need to
convert one field in one table. I bring this table into access as a text
field, should I change this?

 

[Dirk Goldgar]

Thank you very much. I'm sure this is exactly what I need but being
I'm still kinda of new to access, I must be missing where I actually
direct the code towards the particular table and field that I need to
convert. I only need to convert one field in one table. I bring this
table into access as a text field, should I change this?

First copy and paste the code into a standard module. If you don't
already have a standard module defined to contain such utility routines,
create a new one, paste the code into it, and save the module. Don't
name the module the same as the function -- you can use whatever name
Aceess proposes ("Module{n}"), or you can give it your own name. For
general utility routines, I usually use a name like "basUtilities",
where the "bas" prefix is to identify it as a Basic-language module.

Now you need to convert the field in the table. The simplest and safest
way to do this is to create a new field in the table and give it a
numeric data type. If these are integer (whole number) values, probably
the "Number" Field Type, "Long Integer" Field Size is a good choice.
But you might use Currency type, or one of the floating-point field
sizes Single or Double.

Let's suppose your input text field is called "ZonedField", and you name
the output field "ConvertedField". Then you can use an update query to
convert this field for all the records. The SQL of such a query would
look like this:

UPDATE [YourTableName]
SET ConvertedField = fncZonedToNumber(ZonedField);

If you run this query, it ought to convert all those zoned text fields
to numbers stored in ConvertedField.