IIf statement has inconsistent results

[Lisa]

I have a query that compares 3 numeric fields to see if the match. Some give
the correct answer and some do not. An example of the IIf statement is
below. Column RCSELL is typed as a whole number with no decimal places and
CUR-SELL AND FUT-SELL are type will decimal that is why i have to multiple
those by 100. All columns are set up as numbers. Some results are correct
such as 1299 = 1299 giving a YES response, but others like 1799 = 1799
results in a NO answer.


Expr1:
IIf([RCSELL]=([FUR-SELL]*100),"YES",IIf([RCSELL]=([CUR-SELL]*100),"YES","NO"))

 

[Tom Wickerath]

Hi Lisa,

Most likely due to a floating point issue. Please see this article:

When Microsoft Access Math Doesn't Add Up
http://www.fmsinc.com/tpapers/math/index.html


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

 

[John W. Vinson]

I have a query that compares 3 numeric fields to see if the match. Some give
the correct answer and some do not. An example of the IIf statement is
below. Column RCSELL is typed as a whole number with no decimal places and
CUR-SELL AND FUT-SELL are type will decimal that is why i have to multiple
those by 100. All columns are set up as numbers. Some results are correct
such as 1299 = 1299 giving a YES response, but others like 1799 = 1799
results in a NO answer.


Expr1:
IIf([RCSELL]=([FUR-SELL]*100),"YES",IIf([RCSELL]=([CUR-SELL]*100),"YES","NO"))

If RCSELL is typed as a Number... Long Integer, it may be that your Decimal
field still has some trailing decimals. You can also use OR logic to avoid the
nested IIFs. Try

IIf([RCSELL]=CLng([FUR-SELL]*100) OR [RCSELL]=CLng([CUR-SELL]*100),"YES","NO")

 

[Pieter Wijnen]

What about rounding issues?
Fix() or Int() might be required instead of Clng().

Pieter

I have a query that compares 3 numeric fields to see if the match. Some
give
the correct answer and some do not. An example of the IIf statement is
below. Column RCSELL is typed as a whole number with no decimal places
and
CUR-SELL AND FUT-SELL are type will decimal that is why i have to multiple
those by 100. All columns are set up as numbers. Some results are
correct
such as 1299 = 1299 giving a YES response, but others like 1799 = 1799
results in a NO answer.


Expr1:
IIf([RCSELL]=([FUR-SELL]*100),"YES",IIf([RCSELL]=([CUR-SELL]*100),"YES","NO"))

If RCSELL is typed as a Number... Long Integer, it may be that your
Decimal
field still has some trailing decimals. You can also use OR logic to avoid
the
nested IIFs. Try

IIf([RCSELL]=CLng([FUR-SELL]*100) OR
[RCSELL]=CLng([CUR-SELL]*100),"YES","NO")

 

[John W. Vinson]

What about rounding issues?
Fix() or Int() might be required instead of Clng().

Good point. Not sure what the OP has in this field and what constitutes a
"match"!

 

[Lisa]

Thank you so much i just used a round formula and now i am getting the
correct results.

Expr1:
IIf(Round([RCSELL],0)=Round([FUR-SELL]*100,0),"YES",IIf(Round([RCSELL],0)=Round([CUR-SELL]*100,0),"YES","NO"))
--
lmo

Hi Lisa,

Most likely due to a floating point issue. Please see this article:

When Microsoft Access Math Doesn't Add Up
http://www.fmsinc.com/tpapers/math/index.html


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

I have a query that compares 3 numeric fields to see if the match. Some give
the correct answer and some do not. An example of the IIf statement is
below. Column RCSELL is typed as a whole number with no decimal places and
CUR-SELL AND FUT-SELL are type will decimal that is why i have to multiple
those by 100. All columns are set up as numbers. Some results are correct
such as 1299 = 1299 giving a YES response, but others like 1799 = 1799
results in a NO answer.


Expr1:
IIf([RCSELL]=([FUR-SELL]*100),"YES",IIf([RCSELL]=([CUR-SELL]*100),"YES","NO"))