UnPacking Number - Progress, I Think...

[D Smith]

I have a file with packed numbers that I need to convert into decimal numbers.
I've written my 1st-ever Access Function but it does not return a result and
I get an error when I try it in Immediate Mode.

A "packed" number replaces the last (right-most) digit with a letter to
indicate both value and sign:
{=0 }=-0
A=1 J=-1
B=2 K=-2
C=3 L=-3
D=4 M=-4
E=5 N=-5
F=6 O=-6
G=7 P=-7
H=8 Q=-8
I=9 R=-9

So that 4950E = $495.05 and 4950N = -$495.05

My function so far:

Public Function UnPack(InNum As String)
Dim lastc As String -- lastc is used to store the last character
Dim firstc As String -- firstc is used to store the rest of the number
firstc = Left(InNum, 7) -- the numbers are all zero-filled: 0004950E
lastc = Right(InNum, 1)
Select Case lastc
Case "{"
InNum = (firstc + "0")
Case "A"
InNum = (firstc + "1")
Case "B"
InNum = (firstc + "2")
Case "C"
InNum = (firstc + "3")
Case "D"
InNum = (firstc + "4")
Case "E"
InNum = (firstc + "5")
Case "F"
InNum = (firstc + "6")
Case "G"
InNum = (firstc + "7")
Case "H"
InNum = (firstc + "8")
Case "I"
InNum = (firstc + "9")
Case "}"
InNum = (firstc + "0")
Case "J"
InNum = (firstc + "1")
Case "K"
InNum = (firstc + "2")
Case "L"
InNum = (firstc + "3")
Case "M"
InNum = (firstc + "4")
Case "N"
InNum = (firstc + "5")
Case "O"
InNum = (firstc + "6")
Case "P"
InNum = (firstc + "7")
Case "Q"
InNum = (firstc + "8")
Case "R"
InNum = (firstc + "9")

End Select
End Function



When I try to use this in a query, I get no results.
I know I'm missing something here but I don't know what...

Any thoughts?

Thanks!

Dave

 

[Dirk Goldgar]

I have a file with packed numbers that I need to convert into decimal
numbers. I've written my 1st-ever Access Function but it does not
return a result and I get an error when I try it in Immediate Mode.

A "packed" number replaces the last (right-most) digit with a letter
to indicate both value and sign:
{=0 }=-0
A=1 J=-1
B=2 K=-2
C=3 L=-3
D=4 M=-4
E=5 N=-5
F=6 O=-6
G=7 P=-7
H=8 Q=-8
I=9 R=-9

So that 4950E = $495.05 and 4950N = -$495.05

My function so far:

Public Function UnPack(InNum As String)
Dim lastc As String -- lastc is used to store the last character
Dim firstc As String -- firstc is used to store the rest of the
number firstc = Left(InNum, 7) -- the numbers are all zero-filled:
0004950E
lastc = Right(InNum, 1)
Select Case lastc
Case "{"
InNum = (firstc + "0")
Case "A"
InNum = (firstc + "1")
Case "B"
InNum = (firstc + "2")
Case "C"
InNum = (firstc + "3")
Case "D"
InNum = (firstc + "4")
Case "E"
InNum = (firstc + "5")
Case "F"
InNum = (firstc + "6")
Case "G"
InNum = (firstc + "7")
Case "H"
InNum = (firstc + "8")
Case "I"
InNum = (firstc + "9")
Case "}"
InNum = (firstc + "0")
Case "J"
InNum = (firstc + "1")
Case "K"
InNum = (firstc + "2")
Case "L"
InNum = (firstc + "3")
Case "M"
InNum = (firstc + "4")
Case "N"
InNum = (firstc + "5")
Case "O"
InNum = (firstc + "6")
Case "P"
InNum = (firstc + "7")
Case "Q"
InNum = (firstc + "8")
Case "R"
InNum = (firstc + "9")

End Select
End Function



When I try to use this in a query, I get no results.
I know I'm missing something here but I don't know what...

Any thoughts?

Thanks!

Dave

You never assign any return value to the function. You need a statement
like this:

UnPack = InNum ' return converted value as String

or this:

UnPack = CLng(InNum) ' return converted value as Long

.... before exiting the function.

Also, if you're going to use the input parameter directly as a work
variable, you'd better change your function declaration to:

Public Function UnPack(ByVal InNum As String)

By the way, that format isn't what is normally called "packed" -- it's
"zoned".

 

[Roger Carlson]

In addition to Dick's reply, you should also set the datatype of the return
variable:

Public Function UnPack(InNum As String) As String
or
Public Function UnPack(InNum As String) As Currency
etc.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[D Smith]

Thanks Dirk! Your code did the trick!
I then realized that all the fields are NOT the same length so I updated the
function to allow for differing field lengths:

Public Function UnPack(ByVal InNum As String)
Dim lastc As String
Dim firstc As String
Dim FLength As Integer
FLength = Len(InNum) 'gets number of characters in field
firstc = Left(InNum, (FLength - 1)) 'get the correct number of characters
lastc = Right(InNum, 1)
Select Case lastc
Case "{"
InNum = (firstc + "0")
Case "A"
InNum = (firstc + "1")
Case "B"
InNum = (firstc + "2")
---SNIP---
Case "R"
InNum = (firstc + "9")
End Select
UnPack = InNum
End Function

Thanks again - this will same me TONS of work!

Dave

 

[Dirk Goldgar]

Thanks Dirk! Your code did the trick!
I then realized that all the fields are NOT the same length so I
updated the function to allow for differing field lengths:

Public Function UnPack(ByVal InNum As String)
Dim lastc As String
Dim firstc As String
Dim FLength As Integer
FLength = Len(InNum) 'gets number of characters in field
firstc = Left(InNum, (FLength - 1)) 'get the correct number of
characters lastc = Right(InNum, 1)
Select Case lastc
Case "{"
InNum = (firstc + "0")
Case "A"
InNum = (firstc + "1")
Case "B"
InNum = (firstc + "2")
---SNIP---
Case "R"
InNum = (firstc + "9")
End Select
UnPack = InNum
End Function

Thanks again - this will same me TONS of work!

Great, Dave. I don't see where you're picking up the sign, though. If
your last character is one the ones on the "negative" list, you should
probably set a minus sign on the front of the value:

'---- start of revised code snippet -----
Public Function UnPack(ByVal InNum As String)

Dim lastc As String
Dim firstc As String
Dim strSign As String
Dim FLength As Integer

FLength = Len(InNum) 'gets number of characters in field
firstc = Left(InNum, (FLength - 1)) 'get the correct number of
characters
lastc = Right(InNum, 1)
Select Case lastc
Case "{"
InNum = (firstc + "0")
Case "A"
InNum = (firstc + "1")
Case "B"
InNum = (firstc + "2")

' ... rest of positive cases are similar ...

Case "}"
InNum = (firstc + "0")
strSign = "-"
Case "J"
InNum = (firstc + "1")
strSign = "-"
Case "K"
InNum = (firstc + "2")
strSign = "-"

' ... rest of negative cases are similar ...

End Select

UnPack = strSign & InNum

End Function
'---- end of revised code snippet -----

Note that I would use the & operator for string concatenation, rather
than the + operator; however, the + operator should work fine in this
case.

 

[D Smith]

Thanks guys!
Here's the final version of my function.
I'm able to use it in an Append Query where I take the raw version of the
imported text file and copy it to a table I will use to generate various
reports.
Thanks again!

Public Function UnZone(ByVal InNum As String)
Dim lastc As String
Dim firstc As String
Dim FLength As Integer

FLength = Len(InNum)
firstc = Left(InNum, (FLength - 1))
lastc = Right(InNum, 1)

Select Case lastc
Case "{"
InNum = (firstc + "0")
InNum = CCur(InNum * 0.01)
Case "A"
InNum = (firstc + "1")
InNum = CCur(InNum * 0.01)
Case "B"
InNum = (firstc + "2")
InNum = CCur(InNum * 0.01)
Case "C"
InNum = (firstc + "3")
InNum = CCur(InNum * 0.01)
Case "D"
InNum = (firstc + "4")
InNum = CCur(InNum * 0.01)
Case "E"
InNum = (firstc + "5")
InNum = CCur(InNum * 0.01)
Case "F"
InNum = (firstc + "6")
InNum = CCur(InNum * 0.01)
Case "G"
InNum = (firstc + "7")
InNum = CCur(InNum * 0.01)
Case "H"
InNum = (firstc + "8")
InNum = CCur(InNum * 0.01)
Case "I"
InNum = (firstc + "9")
InNum = CCur(InNum * 0.01)
Case "}"
InNum = (firstc + "0")
InNum = CCur(InNum * 0.01)
Case "J"
InNum = (firstc + "1")
InNum = CCur(InNum * -0.01)
Case "K"
InNum = (firstc + "2")
InNum = CCur(InNum * -0.01)
Case "L"
InNum = (firstc + "3")
InNum = CCur(InNum * -0.01)
Case "M"
InNum = (firstc + "4")
InNum = CCur(InNum * -0.01)
Case "N"
InNum = (firstc + "5")
InNum = CCur(InNum * -0.01)
Case "O"
InNum = (firstc + "6")
InNum = CCur(InNum * -0.01)
Case "P"
InNum = (firstc + "7")
InNum = CCur(InNum * -0.01)
Case "Q"
InNum = (firstc + "8")
InNum = CCur(InNum * -0.01)
Case "R"
InNum = (firstc + "9")
InNum = CCur(InNum * -0.01)
End Select
UnZone = InNum
End Function