Solved: Convert zoned decimal field to plain numbers

[dssxpert]

I have a table with a field that has zoned decimals strings, with the field's last character as{/A/B/C/D/E/F/G/H/I/J/K/L/M/N/O/P/Q/R which represent +0/+1/+2/+3/+4/+5/+6/+7/+8/+9/-0/-1/-2/-3/-4/-5/-6/-7/-8/-9 respectively. Here is a sample of what the field values look like:
Charge..........conversion
00000327N = -32.75
00000327E = 32.75
00000305{ = 30.50
I now need to parse out this field's components (negative signs need to go in front of the new value) and covert the value to real numbers (drop leading zeroes; plus sign not needed). The field Charge is currently a text field length 9. I guess the target field, Charges, will need to be either single or double precision numeric. I have 12 fiscal period tables, with about 1 million records in each table. Can anyone help a newbie to Access VBA get this done? Thanks.
George

[OBP]

George, I can help you with this, 1,000,000 records will take a while to run though. Can you attach a zipped sample database with a table with the field and some data in it (not a 1,000,000 records though :))?
I will have a look at it.

[dssxpert]

OBP, I've attached a zipped mdb, Utils.zip, with two records of each type of zoned decimal in one table, and the lookup values in another table. Actually I have another field I need to perform this conversion on too, called OriVolume, but I figure if you do it for Charge, I should be able to figure out how to do it for OriVolume! Thanks.

George

[OBP]

George, I notice in your first example that when the character is "N" which represents -5 that you do not subtract 5 but add 5 and make the whole number a minus. Is this correct thinking.

[Cyberdude]

Here?s my solution:
Sub CnvNumbs()
Dim EndDigit As Single
Dim N As Long
Dim Strg(1 To 3) As String
Dim CurrZone As String
Dim Sign As Single
Dim FinalNum As Single
Strg(1) = "00000327O" '= -32.75
Strg(2) = "00000327E" ' = 32.75"
Strg(3) = "00000305{" ' = 30.50
' {/ A/ B/ C/ D/ E/ F/ G/ H/ I/ J/ K/ L/ M/ N/O/ P/Q/ R/ S"
' +0/+1/+2/+3/+4/+5/+6/+7/+8/+9/-0/-1/-2/-3/-4/-5/-6/-7/-8/-9"
For N = 1 To 3
CurrZone = Right(Strg(N), 1)
Select Case CurrZone
Case "{": Sign = 1: EndDigit = ".00"
Case Is < "J"
Sign = 1
Select Case CurrZone
Case "A": EndDigit = ".01"
Case "B": EndDigit = ".02"
Case "C": EndDigit = ".03"
Case "D": EndDigit = ".04"
Case "E": EndDigit = ".05"
Case "F": EndDigit = ".06"
Case "G": EndDigit = ".07"
Case "H": EndDigit = ".08"
Case "I": EndDigit = ".09"
End Select
Case Is > "I"
Sign = -1
Select Case CurrZone
Case "J": EndDigit = ".00"
Case "K": EndDigit = ".01"
Case "L": EndDigit = ".02"
Case "M": EndDigit = ".03"
Case "N": EndDigit = ".04"
Case "O": EndDigit = ".05"
Case "P": EndDigit = ".06"
Case "Q": EndDigit = ".07"
Case "R": EndDigit = ".08"
Case "S": EndDigit = ".09"
End Select
End Select
FinalNum = Sign * ((Left(Strg(N), Len(Strg(N)) - 1)) / 10 + EndDigit)
MsgBox Format(FinalNum, "0.00;-0.00")
Next N
End Sub
Hope this helps.
Sid

[OBP]

George, here is the database. I have added 3 forms, 2 queries and an autoexec macro to open the 3rd form.
One query/form is for encoding and the other query/form is for the raw data.
The conversion Form has VBA that converst both Volume and Charge and puts it in your new fields. I have changed a couple of records to test 4 types of conversion in the Volumes field. I copied the records to make the table 1080 records long.
The conversion takes just over 2 seconds for the1000 records.

Cyberdude, perhaps you would like to take a look at the String manipulation and use of the "instr" function. :yes

[dssxpert]

Thanks Sid. Yes, you have the correct thinking! Now how do I make your procedure read through the whole table and store FinalNum in a field called Charges?

[OBP]

dssexpert, does the database that I posted do what you want?

[dssxpert]

The code appears that it will work but I still need to know the code for connecting to my database and cycling through each record. The code you posted only cycles through 3 hardcoded examples. I only see code you have posted not a database. Also I don't know if this will cause you to modify your code but the Charge string included two implied decimals. Your case statements kind of hard code the first decimal as 0, which may not be the case in the real number. In other words, EndDigit should only represent one digit.

[OBP]

George, you are mixing me up with cyberdude.
In post #6 I attached a database with a complete resolution for your problem, all you need to do is import the Items outline in the post in to your database(s) and click the command button on the conversion form.
Did you read post #6?

[dssxpert]

OBP, my apologies for mixing you up with cyberdude. I also missed your post #6 the first time because I saw my filename "utils.zip" and mistakenly thought it was the one I sent. I'm kind of new to vbmax but I should have paid better attention. I just got done unzipping your database and running your Data Conversion Form and it's almost perfect! The numeric strings in OriVolume and Charge are implied two decimal, so I believe your results need to be divided by 100 or the equivalent. For example, in the first record the Charge string is 00000752E and should result in the value 75.25, not 7525. Hope this makes sense. You can tell me how to modify Private Sub Command0_Click() or just send me the whole database again. This is really going to help at work, I eagerly await your reply!

One more question. I see that you have Dim'd number As Double, though I've defined Charges and Volumes as Single. I don't know how much space Single is going to save but remember my 12 files are each about 1 million records. Does it matter that you used Double or does it need to be changed to Single?
George

[dssxpert]

Sid,
It looks like OBP's solution is going to work for me, but if you can help me finish your code, it will be easier for me to understand and use on other projects. Please refer to posts 7 and 9 for my comments. Below I have attempted some modification to your code. What I need help with is
1) The statements to connect to the database and read in field Charge
2) The Do While syntax
3) Making sure the implied two decimals are read and written correctly
4) Writing out FinalNum to the field Charges

Sub CnvNumbs()
Dim EndDigit As Single
Dim N As Long
'Dim Strg(1 To 3) As String
Dim Strg As String
Dim CurrZone As String
Dim Sign As Single
Dim FinalNum As Single
'Next 5 statements connect to database and read field
Set rst = New ADODB.Recordset
Set cnn = New ADODB.Connection
Set cnn = CurrentProject.Connection
rst.Open "P0601CONCAT"
Strg = rst!Charge
'Strg(1) = "00000327O" '= -32.75
'Strg(2) = "00000327E" ' = 32.75"
'Strg(3) = "00000305{" ' = 30.50
' {/ A/ B/ C/ D/ E/ F/ G/ H/ I/ J/ K/ L/ M/ N/O/ P/Q/ R/ S"
' +0/+1/+2/+3/+4/+5/+6/+7/+8/+9/-0/-1/-2/-3/-4/-5/-6/-7/-8/-9"
Do While Not EOF()
'For N = 1 To 3
'CurrZone = Right(Strg(N), 1)
CurrZone = Right(Strg, 1)
Select Case CurrZone
Case "{": Sign = 1: EndDigit = ".00"
Case "}": Sign = -1: EndDigit = ".00"
Case Is < "J"
Sign = 1
Select Case CurrZone
'There are two implied deicmal points in the string, but the
'last character only refers to one of them, e.g. For case A
'the ending two digits would be the 8th character of the
'string and 1, not 01. Maybe your code handles this
'correctly, but I can't tell.
Case "A": EndDigit = ".01"
Case "B": EndDigit = ".02"
Case "C": EndDigit = ".03"
Case "D": EndDigit = ".04"
Case "E": EndDigit = ".05"
Case "F": EndDigit = ".06"
Case "G": EndDigit = ".07"
Case "H": EndDigit = ".08"
Case "I": EndDigit = ".09"
End Select
Case Is > "I"
Sign = -1
Select Case CurrZone
Case "J": EndDigit = ".01"
Case "K": EndDigit = ".02"
Case "L": EndDigit = ".03"
Case "M": EndDigit = ".04"
Case "N": EndDigit = ".05"
Case "O": EndDigit = ".06"
Case "P": EndDigit = ".07"
Case "Q": EndDigit = ".08"
Case "R": EndDigit = ".09"
End Select
End Select
'FinalNum = Sign * ((Left(Strg(N), Len(Strg(N)) - 1)) / 10 + EndDigit)
FinalNum = Sign * ((Left(Strg, Len(Strg) - 1)) / 10 + EndDigit)
'MsgBox Format(FinalNum, "0.00;-0.00")
rst!Charges = FinalNum
'Next N
End While
End Sub

Thanks, George

[dssxpert]

OBP,
I just ran your Data Conversion Form against my first million record table and after a few seconds got the message "Overflow". Any ideas on how to mitigate this?

[OBP]

George, this is a new version of the database. I have added the division by 100 to the calculations. I think the "Overflow" problem is due to the fact that I used an Integer for the record counter, which runs out at about 32767 I think (forgot the million:(). You should be able to check the table and see how far it got with the conversion.
The double precision number in the VBA only uses a little more RAM, not disc space, so it shouldn't be a problem, you can re Dim it to a Single if you like.

[dssxpert]

:clap: OBP, I still had to fiddle with tne Dim of recount to Long, and count required the same datatype as recount because of 'For count = 1 To recount', but hey, long story short is that it's working! After perusing several newsgroup posts on 'Access Overflow' I was able to debug version 1.1 while I was waiting for your answer on 1.2. I still understand only about 50% of your code but hopefully I will be able to grasp more of it over time.

I'm still suprised that I couldn't find anything through Google to solve this problem. There are still lots of people out there that must use mainframe zoned decimal files like me, and we have to import the data into Excel or Access. Anyway your code is a tremendous help to me and I'm sure that other people will find it useful as well when they come across it.

The runtimes on my million record files are running about 40 minutes on average but that's manageable considering your code is the only solution I've got. It seems like having the encoding values in memory would speed things up a bit but I'm still trying to figure out how you used the Encoding Query and Encoding Form as a lookup table.

George

[OBP]

George, having the encoding values in memory won't actually save much time as the first 1000 records only takes a few seconds to run, which includes taking them from the form and putting them in to memory. :)
What did surprise me is that just adding the 2 division calculations increased the running time for the 1000 records by 50%. I could look at reducing the time for you.
How often do you have to run this conversion?

I have just detected an error in the code that may be increasing the data processing time, if it is I will post a new faster version for you.

[dssxpert]

I wouldn't say it is often that I have to run this conversion, but when I do run it I have to process 12 monthly tables at a time--about 12 million records, which is a drag, timewise. And conversion is just one step in a multi-step process performed on these tables. Any time savings would be wonderful!

[OBP]

George, hopefuly I wil have something much faster for you tomorrow, it is based on running queries instead of VBA, which run much faster.
Can you do a test for me on a million record table?

[dssxpert]

Yes, I can do a test on a million records, no problem.

[dssxpert]

Sid,

While OBP's solution is working for me, I have cleaned up your code a little and added what I think is the correct terminology for opening and reading my table. I have pasted the code below. Unfortunately I get the following error message when I run it:
Run-time error "3251";
Current recordset does not support updating. This may be a limitation of the provider, or of the selected locktype.

Sub CnvNumbs()
Dim EndDigit As Single
Dim N As Long
Dim Strg As String
Dim CurrZone As String
Dim Sign As Single
Dim FinalNum As Single
Dim rst As New ADODB.Recordset
Dim cnn As ADODB.Connection
Set cnn = CurrentProject.Connection
rst.Open "P0601CONCAT", cnn, adOpenDynamic, , adCmdTable
Do Until rst.EOF
Strg = rst!Charge
CurrZone = Right(Strg, 1)
Select Case CurrZone
Case "{": Sign = 1: EndDigit = ".00"
Case "}": Sign = -1: EndDigit = ".00"
Case Is < "J"
Sign = 1
Select Case CurrZone
Case "A": EndDigit = ".01"
Case "B": EndDigit = ".02"
Case "C": EndDigit = ".03"
Case "D": EndDigit = ".04"
Case "E": EndDigit = ".05"
Case "F": EndDigit = ".06"
Case "G": EndDigit = ".07"
Case "H": EndDigit = ".08"
Case "I": EndDigit = ".09"
End Select
Case Is > "I"
Sign = -1
Select Case CurrZone
Case "J": EndDigit = ".01"
Case "K": EndDigit = ".02"
Case "L": EndDigit = ".03"
Case "M": EndDigit = ".04"
Case "N": EndDigit = ".05"
Case "O": EndDigit = ".06"
Case "P": EndDigit = ".07"
Case "Q": EndDigit = ".08"
Case "R": EndDigit = ".09"
End Select
End Select
FinalNum = Sign * ((Left(Strg, Len(Strg) - 1)) / 10 + EndDigit)
'MsgBox Format(FinalNum, "0.00;-0.00")
rst!Charges = FinalNum
rst.MoveNext
Loop
End Sub

Any idea on how to get it to update field Charges?
George

[OBP]

George, this first version is just an update of the original with faster code, it should reduce the time taken to convert by about 25%.

[OBP]

George, this version is the best that I have been able to achieve, it uses queries instead of VBA to make the conversions.
It converts 100,000+ records in 1.9 minutes on my computer, which is running 3.06Ghz.
It has no tables so you need to "import" one of your million record tables and rename "data" for it to work. You need to do that before looking at any queries to prevent it messing them up.

[dssxpert]

OBP, I imported a table and renamed it to data, and then ran your new form/queries I only had time on Friday before I left work to test one fiscal period. Unfortunately the time was not encouraging. The average of the previous 9 fiscal periods was 2,396 secords (40 minutes), whereas this file took 3,607 seconds (60 minutes), a 50% increase in processing time. I will try it on two more files on Monday. Thanks for your efforts.

[OBP]

George, that is very disappointing, seeing as how quick it was on my 100,000 record file.
Perhaps on monday you could try the other one the I posted. It should be faster than the original VBA as it is processing less code.

[dssxpert]

OBP, the timing on month 11 was 2761 (46 minutes) and month 12 was 3165 (53 minutes). It would still seem that your first solution, in Utils v 1.2 b.mdb, is somewhat faster. I can certainly live with it! Thanks again.
George

[OBP]

George, did you try the intermediate version?
It has basically the same VBA as the first version with a few less lines of code so it should be a bit faster.:dunno