views:

99

answers:

2

I am attempting to emulate a (no longer existing) mainframe report generator in an Access 2003 or Access 2010 environment. The data it generates must match exactly with paper reports from the early 70s. Unfortunately, the earliest years data were run on hardware that used IBM floating point representation instead of IEEE. With the help of Google, I've found a library of VBA functions that will convert a float from decimal to the IEEE 754 32bit binary format. I had to modify the library to accept either 32bit or 64bit floats, so I have a modest working knowledge of floating point formats, however, I'm having trouble making the conversion from IEEE to IBM binary format, as well as trouble multiplying and adding either the IBM or the IEEE numbers.

I haven't turned up any other libraries for performing this conversion and arithmetic operations in VBA - is there an easier way to go about this, or an existing library that I'm not finding? Failing that, a clear and straightforward explanation of the relevant algorithms?

Thanks in advance.

+1  A: 

The problem is here's your confusing the issue of decimal type in access, and that of single and double type floating point values available in access.

If you use the currency data type in access, this is a scaled integer, and will not produce rounding (that is what most of us use for financial calculations and reports). You can also use decimal values in access, and again they don't round at all as they are packed decimals.

However, both the single and double values available inside of access are in fact the same format and conform to the IEEE floating point standard.

For an access single variable, this is a 32bit number, and the range is:

-3.402823E38 
    to 
-1.401298E-45 for negative values

and

1.401298E-45 
    to 
3.402823E38 for positive values

That looks to be the same to me as the IEEE 754 standard.

So, if you add up values in access as a single, you should get the rouding same results.

So, Intel based, and Access single and doubles I believe are the same as this IEEE standard.

The only real issue it and here is what is the format of the original data you're pulling into access, and what kinds of text or string or conversion process is occurring when that data is pulled in and stored?

Access can convert numbers. Try typing these values at the access command line prompt (debug window)

 ? hex(255)
     Above will show  FF

 ? csng(&hFF)
     Above will show 255

Edit:

Ah, ok, I see now I have this reversed, my wrong here. The problem here is assuming you convert a number to the older IBM format (Excess 64?), you will THEN have to get your hands on their code that they used for adding those numbers. In fact, even back then, different IBM models depending on what you purchased actually produced different results (more money = more precision).

So, not only do you need conversion routines to convert to the internal representation, you THEN need the routines that add/subtract/multiply those numbers. So, just having conversion routines is not going to get you very far, since you also have to duplicate their exact routines that do math. Those types of routines are likely not all created equal in terms of how they round numbers etc.

Albert D. Kallal
I don't think it's the case that I'm confused about singles/doubles in Access. I know that they are both the same format. The problem is that I need to match numbers that were generated by a system that did *not* use the IEEE 754 format. The numbers going into Access are fixed-point text format (123.456, for instance, always with 3 decimal places). The difficulty is that there are calculations that multiply three of these #s and then add thousands of them together - I need to emulate the precision errors that occurred on the original hardware/software.
Kevin Hobbs
Ah, yes, I did read this wrong. See my edit + further comments here. I think the conversion to IBM format might not be too hard, but that is only a small portion of the problem. Finding out the internal precision is easy, but finding out how the math routines work and how they handled rounding is going to be a good deal more difficult here.
Albert D. Kallal
+1  A: 

To be honest you'd probably do better to start by looking at the Hercules emulator. http://www.hercules-390.org/ Other than that in theory with VBA you can use the Decimal type to get good results (note you have to CDec to create these) it uses 12 bits with a variable power of ten scalar.

A quick google shows this post from the hercules group, which confirms Alberts point about needing to know the hardware:

---Snip--

In theory, but rather less so in practice. S/360 and S/370 had a choice of Scientific or Commercial instruction sets. The former added the FP instructions and registers to the base; the latter the decimal instructions, including Edit and Edit & Mark. But larger 360 (iirc /65 and up) and 370 (/155 and up) models had the union of the two, called the Universal instruction set, and at some point the S/370 dropped the option.

---snip---

I have to say that having looked at the hercules source code you'll probably need to figure out exactly which floating point operation codes (in terms of precision single,long, extended) are being performed.

PeterI