calculation with NOT EQUAL TO

[Ray C]

While I'm in the Immediate Window in Access I type the following calculation
and it gives me the wrong answer, what could be causing this?

? 100.70-100.69
1.00000000000051E-02

It should give me 0.01, no?

The reason why I'm doing this is because I have a function that compares
calculated fields and for some reason Access tells me it's not valid when it
is in fact valid.
This function does some comparisons like:

If Me.txtField1 <> Me.txtField2 - Me.txtField3 then
Valid = False
Else
Valid = True
Endif

When I debug, I see the values and it should return True, but instead it
returns False. So I decided to retype the same values in the Immediate window
and I get the above. Which is wrong.

Any help would be appreciated. This is just weird.

Thanks in advance

 

[Keven Denen]

While I'm in the Immediate Window in Access I type the following calculation
and it gives me the wrong answer, what could be causing this?

? 100.70-100.69
 1.00000000000051E-02

It should give me 0.01, no?

The reason why I'm doing this is because I have a function that compares
calculated fields and for some reason Access tells me it's not valid whenit
is in fact valid.
This function does some comparisons like:

If Me.txtField1 <> Me.txtField2 - Me.txtField3 then
     Valid = False
Else
     Valid = True
Endif

When I debug, I see the values and it should return True, but instead it
returns False. So I decided to retype the same values in the Immediate window
and I get the above. Which is wrong.

Any help would be appreciated. This is just weird.

Thanks in advance

Well, floating point calculations are almost always going to be
inaccurate.

Your best bet is to convert any floating point values to Access built
in Decimal type. Most of these errors go away then.

Try doing ? cdec(100.70)-cdec(100.69). It should come out as .01

Keven

 

[Dirk Goldgar]

While I'm in the Immediate Window in Access I type the following
calculation
and it gives me the wrong answer, what could be causing this?

? 100.70-100.69
1.00000000000051E-02

It should give me 0.01, no?

No. Floating-point numbers are imprecise, and calculations involving them
often give imprecise results.

The reason why I'm doing this is because I have a function that compares
calculated fields and for some reason Access tells me it's not valid when
it
is in fact valid.
This function does some comparisons like:

If Me.txtField1 <> Me.txtField2 - Me.txtField3 then
Valid = False
Else
Valid = True
Endif

When I debug, I see the values and it should return True, but instead it
returns False. So I decided to retype the same values in the Immediate
window
and I get the above. Which is wrong.

Any help would be appreciated. This is just weird.

It's generally not a good idea to compare calculated floating-point values
for equality. Instead, decide on the maximum acceptable difference between
them, and check to see if the result of your calculation exceeds that
difference. For example,

? (100.70 - 100.69) = .01
False
? Abs((100.70 - 100.69) - .01) < .000000001
True

 

[Jack Cannon]

Another approach that you might wish to consider is to multiply by 100 and
convert to integer prior to making the comparison.

Jack Cannon

 

[Ray C]

So, if I type a value such as 100.70 in the immediate window, Access
automatically takes it as a floating point number?

My code actually deals with currency values. I declare them as Double in my
code.
Should I have done this? Or should currency values be declared something else?
I have a function called RoundOff(x) which takes a value having many decimal
places and rounds it to 2 decimals, then the result is returned as a Double.
Is this ok? This seemed to have solved my problem. But is there another
easier (or built-in) way?

Thanks in advance.

Ray

 

[Jack Cannon]

Yes. That is correct unless the value is stored as currency. The reason why
is that many decimal numbers cannot be accurately represented in the binary
system so they are converted to floating point. This is not necessarily a
negative attribute of the binary system. For example: the decimal system
cannot accurately represent many numbers either (1/3, 1/7, 1/9, 1/11, etc.).
However, Access does have the positive feature of being able to accurately
represent currency up to three decimal points.

It is not clear to me why you are declaring the values as Double. If your
system is restricted to three decimal points or less then declaring the
values as Currency should accomplish any objective that you may encounter.
Declaring the values as Double ensures that you have a floating point number.

Jack Cannon

 

[John W. Vinson]

So, if I type a value such as 100.70 in the immediate window, Access
automatically takes it as a floating point number?

Yes; but you can use the rather obscure Type Declaration Suffixes:

312.51@

will be treated as a Currency constant. You can also use the CCur() function
to coerce a value to currency:

CCur(vMyVariable * 123.5)

will return a Currency result.

My code actually deals with currency values. I declare them as Double in my
code.

If you want currency... define them as Currency. It's a perfectly valid
datatype!

Should I have done this? Or should currency values be declared something else?
I have a function called RoundOff(x) which takes a value having many decimal
places and rounds it to 2 decimals, then the result is returned as a Double.
Is this ok? This seemed to have solved my problem. But is there another
easier (or built-in) way?

Does it use the builtin Round() function? You could make this a one-liner:

Public Function RoundOff(vIn As Variant) As Currency
If Not IsNumeric(vIn) Then
RoundOff = 0
Else
RoundOff = CCur(Round(vIn, 2))
End If
End Function