• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 518
  • Last Modified:

Working with the Single Data Type, avoid wrong calculations [Expert Hunt 007]

Fellow experts ;)

Here is a question which I din't find answered in this database. As a matter of fact, I had been working with Access for years before I encountered this bug (it cost me several days to figure it out / fix it)...

In a "homegrown" financial database I was redesigning, rates were applied to investments to calculate management fees. Rates were stored as Single (e.g. 0.325%) and invested amounts as Currency (NAV) times Double (Shares).

Simple case:
    Fees = Rate * NAV * Shares

With Fees = 0.325%, NAV = 10,000 Yen, Shares = 10,000,000, you expect:
    ? 0.00325 * 10000 * 10000000
      325000000

But in the application, I had 7 Yen more, or 325,000,007... and you can't do that with Japanese clients :)

The present "expert hunt" is to find out how 007 got into this. Happy Hunting :)

Award: Points will be awarded to the following validation rule:
    Allow (single) rates from 0% to 1% by quarter points increase (0.025%) only.

It is closely related, believe me, and disqualifies VB solutions :)
0
harfang
Asked:
harfang
  • 16
  • 16
  • 12
2 Solutions
 
flavoCommented:
hey hoo harfang!

It looks to me that the precision of the single data type is shoot.  One would expect it to be around 1.401298E-45 (smallest value) however it seems to be much larger than that

See my test :

Sub test()

Dim fees As Single
'Dim NAV As Currency
'Dim Shares As Double
Dim fees2 As Double

fees = 0.00325
'NAV = 10000
'Shares = 10000000

fees2 = fees

Debug.Print fees2
'Debug.Print (fees2 - 0.00275) / 0.00275
End Sub


what's the go????

0
 
flavoCommented:
looks like 1.401298E-45  doesn't go into 0.0025 evenly, hence your problem..

Dave
0
 
flavoCommented:
Now if i remember back to my maths subject, you error is getting propegated.  Noe a max error would be + / - half the precision.  which in your case would be

?1.401298E-45 / 2 * 10000 * 10000000
 7.00649E-35

Now, how that gets turned into 7, im not sure...
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
harfangAuthor Commented:
Getting there, but 1.401298E-45 is the smallest Single value possible, not the precision of 0.235% stored *as* single.
Happy hunting :)
0
 
harfangAuthor Commented:
As for your VB, you are right, of course:

? 0.00325! * 10000# * 10000000@    ' single, double, currency
 325000006.705523

because:

? CDbl(0.00325!)
 3.25000006705523E-03

You provoked the CDbl implicitly by assigning a Single variable to a Double...

Cheers!
0
 
pique_techCommented:
Try this:

Public Sub TestPrecision(SglVal As Single, DecVal As Variant)

    Dim s As Single
    Dim d As Variant
    s = SglVal
    d = DecVal
   
    Debug.Print s * 10000 * 10000000
    Debug.Print d * 10000 * 10000000
   
    Debug.Print (s - d) * 10000 * 10000000

End Sub

(In the immediate window)
TestPrecision .00325,.00325
 325000006.705523
 325000000
 6.70552255215007

Apparently the precision with with a Single in Access (probably VBA/VB in general) stores the value 0.00325 = ~6.7/100000000000  ~~7/100000000000  (that's 7 one-hundred-billionths)

This is probably why SQL BOL says, if you REALLY need it exactly right, store it as currency or as decimal.

Is that closer, Mr. Other Experts?!   ; )
0
 
flavoCommented:
hmm seeing a single stores 4 bytes of data (or 8 numbers) it can only be as good as the first 8 digits of your answer (from physics back at high school and the teacher going on and on about significant figures)

so

325,000,007
123,456,789

we should only "look" at

325,000,000 with an error of + / - 50

or something like that...  
0
 
pique_techCommented:
This has to do with the way decimals must be represented by binaries, but I'm to tired to do the actual conversion.  Roughly, though, 0.00325 = 325 X 10^-5.  It's easy to convert 325 to binary (101000101), but
10 ^ -5 is (2 ^ 3.16227766016838) ^ -5, or 2 ^ -15.8113883008419.  But because the square root of 10 is irrational, there is no EXACT binary equivalent.  (In fact, there's no exact decimal equivalent.)
0
 
harfangAuthor Commented:
OK, I think the first part of the quest is over...

> How did 007 manage his trick?
Because Single values store (roughly) seven digits and an exponent. So that the value
    0.00325
is in fact something like
    3.250000E-3
or, converted again,
    0.003250000???????????????????????????????????????????

The remaining digits are left for the convert function to decide...

We alread know that CDbl(), used explicitly or implicitly does a bad job. Is there something better?

    ? ConvertToDouble(0.00325!) * 100000#
      325

Cheers!
0
 
pique_techCommented:
CDec or CCur
0
 
flavoCommented:
Thats the money!
0
 
flavoCommented:
>> Thats the money!

ie

Good work pique
0
 
flavoCommented:
Now,

why does cDec work (12bytes) while cdbl (8 btyes)??

Wouldnt it be the same??

Sub test()

Dim fees As Single


fees = 0.0025

'NAV = 10000
'Shares = 10000000
Debug.Print CDbl(fees!)
Debug.Print CDec(fees!)
Debug.Print CDec(CDbl(fees!))
End Sub


produces

 2.49999994412065E-03
 0.0025
 0.00249999994412065

hmmmm....
0
 
pique_techCommented:
CDec is *FAR* more precise than CCur
0
 
harfangAuthor Commented:
I could add that Single values work fine with other single values and with integers... But not other types! Implicit data typing and conversion is weird.

    ? 0.00325! * 10000   ' integer by default
     32.5
    ? TypeName(0.00325! * 10000)
    Single

    ? 0.00325! * 100000   ' double because > 2^15-1
     325.000006705523
    ? TypeName(0.00325! * 100000)
    Double

Cheers!
0
 
pique_techCommented:
It's because of the binary representation of decimal numbers.  Decimal and Currency force a much more precise (but still inexact) representation of the fractional parts of numbers.

From the VBA help file:
Decimal data type
A data type that contains decimal numbers scaled by a power of 10. For zero-scaled numbers, that is, numbers with no decimal places, the range is +/-79,228,162,514,264,337,593,543,950,335. For numbers with 28 decimal places the range is +/-7.9228162514264337593543950335. The smallest non-zero number that can be represented as a Decimal is 0.0000000000000000000000000001.

Note that at this time the Decimal data type can only be used within a Variant. You cannot declare a variable to be of type Decimal. You can, however, create a Variant whose subtype is Decimal using the CDec function.

Currency data type
A data type with a range of -922,337,203,685,477.5808 to 922,337,203,685,477.5807. Use this data type for calculations involving money and for fixed-point calculations where accuracy is particularly important. The at sign (@) type-declaration character represents Currency in Visual Basic.
0
 
harfangAuthor Commented:
but WE HAVE A WINNER! CDec() is the way to go!

    ? CDec(0.00325!)
     0.00325
    ? CCur(0.00325!)
     0.0033

Currency values are limited to four decimals... (this is why I chose this rate as example :)

RULE: When using Single values in your database, consider always using CDec(sngValue) when you use it in calculations. The default or explicit conversion to double might not procude the expected result!

Now my problem.... Access 97 does not recognize CDec(), while Access 2000 uses a non VB CDec() with two parameters.... What to do?

:)
0
 
flavoCommented:
Access 97 does use cDec, its what i have.. It doesnt "turn blue" but it works

as pique said before (somes from A97 too)

Note   At this time the Decimal data type can only be used within a Variant, that is, you cannot declare a variable to be of type Decimal. You can, however, create a Variant whose subtype is Decimal using the CDec function.
0
 
harfangAuthor Commented:
Maybe this wasn't clear. In validation rules, you can use only SQL compliant functions, not the whole range of VB functions. I know most SQL engines have a decimal data type, but the JetEngine???
:)
0
 
flavoCommented:
>> somes from A97 too)

geess.. fat fingures

comes from A97 too)
0
 
pique_techCommented:
I *did* read your original post about the validation rule...  

Why does this have to be at the table level and not coded somewhere?  'Cause I don't the answer to that one.
0
 
pique_techCommented:
'Cause I don't KNOW the answer to that one.

(Late-night fat fingers here too.)
0
 
harfangAuthor Commented:
I added the twist about the validation rule because CDec was too easy for you :)
Also, you forgot one other conversion function, very basic and SQL compliant, which should be the answer for this FAQ. Well, maybe FAQ thould be ANAQ (for "almost never"), but still, there is an easier way...

Good Luck :)
0
 
flavoCommented:
clng, but there go your cents....
0
 
flavoCommented:
fees - single
cost - double

SELECT (CStr([fees])*[cost]) AS myCost
FROM tblTest;

Conversions flying left, right and cetre!
0
 
harfangAuthor Commented:
AHHHH another winner :)

    ? CStr(0.00325!) * 100000
     325

I just KNEW I would have to split points in the end :)
0
 
flavoCommented:
LOL!

Do we get a job at google now??
0
 
pique_techCommented:
But I don't like CStr for this:  it's unseemly to use a string-oriented operator to maintain numeric integrity.  

(This probably provides more insight about how my mind does/doesn't work than I really intended...)

Now it's my turn to ask what may be a VERY basic question:  How are you going to use any "real" function (i.e., takes arguments) as a table-level validation rule?  I cannot figure out how you're going to configure the table or field to do this without any coding.  I experimented with "It must be an even number" which in my mind, means SomeNumber Mod 2 = 0.  I fooled with all kinds of constructions on the table designer's Validation Rule property and couldn't get anything to work.
0
 
harfangAuthor Commented:
intEven, integer, validation rule: [intEven]\2=[intEven]/2

(but i'm helping here... ;)
0
 
pique_techCommented:
Well, yes, it IS the same question (I knew this before I asked), because what you want is
      SomeVal Mod 0.0025 = 0 And SomeVal Between 0 and 0.01
but you need it exactly accurate rather than single or double precision.

It seems a little kludgy to me, but this seems to do what you need:
CLng([SomeVal]*10000)\25=CLng([SomeVal]*10000)/25 And CLng([SomeVal]*10000) Between 0 And 100

I've never used complex validation at the table level, so I'm pretty ignorant about how to implement it.
0
 
harfangAuthor Commented:
I think you got it... I had something else in mind, though. Be back soon! :)
0
 
harfangAuthor Commented:
OK, time to award the points... :)

The answer I had in mind was:

   Is Null Or Between 0 And 0.01 And Int(Val([sngRate])*4000)=Val([sngRate])*4000

This demonstrates the general rule when useing single values....

    ALWAYS USE Val() WHEN USING A Single VALUE IN CALCULATIONS...

I suspect that Val() is slightly more efficient what CDbl(CStr()), but I still need to award something to flavo, because this is one general solution for queries. On the other hand, pique_tech has provided a validation rule that *should* have worked, except that CLng() is not JetEngine compliant... Of course CLng() can be simulated with Int() by adding 0.5 before converting, so he's almost there...

I found no good way to write the rule without using Val().

Bye for now, I had fun. Thanks for indulging:)
Markus
0
 
harfangAuthor Commented:
Hmmm. got it wrong :(
Sorry, flavo, I meant to split points, but never got the option.
(newbie at asking questions)
I'll see what I can do.
0
 
flavoCommented:
oh well

There is a Split Points above where you write your posts :-)

Dave
0
 
pique_techCommented:
FWIW, I *was* able to enter my suggestion
     CLng([SomeVal]*10000)\25=CLng([SomeVal]*10000)/25 And CLng([SomeVal]*10000) Between 0 And 100
as typed into my table design's field validation rule.  That I'm using Access 2000 probably helps?

You also never mentioned that Nulls would be permitted...  ; )
0
 
harfangAuthor Commented:
True, Access 2000 allows most built-in VB functions (they now share one more DLL I believe), and the twist about Nulls was not in the question.
I did accept your entry for this contest, mind you.
And I still believe that Val() should be the general advice for anyone using the Single data type :)

Cheers! Thanks again for participating!
0
 
pique_techCommented:
It's not about the points (in this case, I really mean that!), but about the interaction.

I see what is (to me) a surprising number of people still using A97.  Do you have any sense for the distribution of users among A97 vs. 2000/XP vs. 2003?  And is this primarily client-driven (i.e., for whatever reasons, a client won't/can't upgrade)?  Just curious what your sense for this is--as you may have sensed, I'm not so good with 97, haven't used it for years and have forgotten most of its quirks.
0
 
harfangAuthor Commented:
I see many setups with Office 2000 + Access 97. Most IT departments simply do not want to upgrade all the homegrown applications. When they tried, it didn't work, and by the time SR2 or so was out, the habit was taken: stick with Access 97.
There are many many things I like about 2k+, but I seem to be stuck for a little while longer :(
0
 
flavoCommented:
Yeah.  We are still stuck with Access 97 (but also use Office 97)
Why??

- Cost too much to update all those systems (as harfang said)
- Cost too much to upgrade 5000 pc's (software)

There are many things that are "easier" to do in A2k+, but after a while, you get a whole bag of functions to do the same thing, so in the end, it doesnt really matter... The only down side is not being able to even open a A2k+..

This was a fun Q harfang, kept the brain ticking :-)
0
 
pique_techCommented:
Thanks for the perspective.  And harfang, this was a great "object lesson" type question.

I work in a gi-normous corporation (180,000 +/-) so that kind of stuff is not what I'm used to.  
I don't have to worry about version compatibility, but I have to deal with stifling expectations of compliance.
I have reliable hardware, but never the newest or best.
I have a DBA to do all the routine "yucky" SQL stuff I hate, but I have absolutely no control over my app's SQL servers.

The list goes on, but I suppose it's all about the trade-offs...   ; )
0
 
harfangAuthor Commented:
Hey, I did split the points... messed the "accepted" vs "assisted", but we all have to learn...

The present question comes from my current job, for the billing department of a large financial company that manages insane amounts of money. No, it's not a Swiss Bank, but they are playing with the idea... :)  Well, their worldwide IT directives are to support Access 97 until the end of 2005 at least. Then they might make the quantic jump to 2003...

In my previous important job, I was developping an application sold to about 150 clients. It manages the "quality documentation" of a company. It is meant for up to half a dozen users entering and maintaining the data and an unlimited number of "read-only" users. It publishes the entire cross-linked documentation as a static HTML folder (can be burnt onto a CD for instance). Well, up to this year (I'm no longer on the project), we had to offer an Access 97 version... The sales pitch included:
- runs on your installed version of Access 97 or 2000
- Access runtime available available for those versions
- Interface in Englich, French or German
- Does not require a server installation, it only needs one public folder

I know that the Access 97 compatibility was important ("our IT people only supports that version"), and also of course that no server installation was required. The competitors all had chosen some type of server based solution, which gave us an enormous advantage :)

Anyway, this makes me an "Access 97 guru". I'm still unfamiliar with ADO :)

Cheers!
0
 
flavoCommented:
ADO... what's ADO????

I jumped a step and now have now grown to like ADO .Net, well the whole .Net framework actually.

Looks like im just a small fry in the land of "mega companies"  My apps are used by a "select few"....

>> - runs on your installed version of Access 97 or 2000

i didnt know you could open a 97 mdb in 2000 without converting, or a mde at all for that mater???

Dave :-)
0
 
harfangAuthor Commented:
No, no. You could do that, but it's an installation option. There are 24 versions on the CD: 2 Access versions, 3 languages, and then all possible combinations of with/without three "modules"... The module issue was to be replaced with an "activation key" mechanism, I don't know if they have done it finally :)

I was basically writing Access 2000 compliant code in Access 97, with a test on the current version for some details ("repair database" no longer available, and such...). When a new release was ready, I had a rather painful process of generating all versions, converting them to Access 2000, and generating MDEs in both versions... :(

Cheers!
0
 
flavoCommented:
I see..  That clears it up...

Sounds like you have some fun projects... Maybe one day ill move over and get a job in IT... I just couldnt be bothered applying for jobs :-)

Dave :-)
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 16
  • 16
  • 12
Tackle projects and never again get stuck behind a technical roadblock.
Join Now