Data Type - Double, wrong value used

[firstdivision]

Hi,

I have a function that (basically) multiplies two numbers together. The two input parameters are Double data types. The values that get put into these parameters come out of a SQL server 2000 database (stored as real).

When I hover over the statement that calls the funtion, the value that comes up in the tool tip is 1.38, which is ther correct value that is stored in the database. I assume that this means that up until this point everything is working as planned...

However, once inside the VBA function the value stored in the Double variable is 1.37999999523163 NOT 1.38. In most circumstances this is not a problem, but every once in a while it results in the calculation being off a cent or two. It's the same for other numbers as well, 2.41 becomes 2.41000008583069.

Is this a result of Access/VBA doing some conversion as it passes the number around? I would really like it to use the number I intended it to and not something else! :-D


Thanks,
FD

 

[firstdivision]

More info:

It does appear to have something to do with the conversion because:

Debug.Print CDbl(objrs.fields("theValue"))

where theValue is 1.38 in the database results in 1.37999999523163 being displayed in the debug window while:

Debug.Print (CDbl("1.38"))
or
Debug.Print (CDbl(1.38))

both result in 1.38 appearing in the debug window....

 

[firstdivision]

I guess it's only when a single is converted to a double. Here's a copy-paste of an experiment done in the debug window:

? CDbl(CSng(1.38))
 1.37999999523163 
? CDbl(1.38) 
 1.38 
? csng(1.38)
 1.38 
? csng("1.38")
 1.38 
? cdbl("1.38")
 1.38

Now I have no idea what to do to fix my problem. Two different functions based on what the data type of the parameters are, single or double?

 

[firstdivision]

Ahhh, I think I finally found a answer, I'll put it here for anyone who's interested:

http://p2p.wrox.com/topic.asp?TOPIC_ID=13427

This is a bummer I think, we'll see how much this effects....