Importing signed mainframe fields

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Does anyone know a way to import signed dollar fields into and Access table?

Examples:

0000700E
0004251C
0000625E
0006290{
 
Jim,
This is overpunch code.
I have dealt with it by importing the data and then running several
queries to split apart and then merge the values.
For example, I import the data into several fields (not records) by
using the fixed import format...
Field 1: '000070'
Field 2: '0'
Field 3: 'E'

Then I simply create a reference table that I can use to query from:
OverPunchCode OverPunchMultiplier OverPunchValue
{ 1 0
} -1 0
0 1 0
A 1 0.01
B 1 0.02
C 1 0.03
D 1 0.04
E 1 0.05
F 1 0.06
G 1 0.07
H 1 0.08
I 1 0.09
J -1 0.01
K -1 0.02
L -1 0.03
M -1 0.04
N -1 0.05
O -1 0.06
P -1 0.07
Q -1 0.08
R -1 0.09

A brighter person could probably write VBA code to do this, but I just
do it through queries.

Bryan
 
Hi Jim,

This function - author unknown - has been posted in the newsgroups a few
times. Start by importing the signed fields into a text field; then add
a number field to the table; next use fncZonedToNumber in an update
query to populate the number field; and finally delete the text field.

You say "signed dollar fields": if this means the last two digits in the
signed field represent cents, your update query will need to divide the
values returned by fncZonedToNumber by 100.


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
 
Back
Top