Incorrect results in Query

[cneill]

Hi,

Re the following in my query

Outcome: IIf([Forecast TCO2]=[Actual TCO2],"",[Forecast TCO2]>[Actual TCO2])

This iif works find in most cases but there are a number of records that meet the first part so should therefore return nothing but the don't. I get both items returned of the same value. Both fields have the same data type.Can anyone suggest where I am going wrong.

Thanks
CNEILL

 

[Remou]

Null is not equal to a zero length string (ZLS). Is it possible that some of your empty fields have null, whereas others have a ZLS or are space filled?

http://lessthandot.com

 

[cneill]

Hi Remou,

Every Forecast tco2 and every Actual tco2 field has a value
so the first part of the iif needs to look at the two values if they are equal then return nothing if not then use the second part.
The problem is that in some cases when the values are equal (well apear to be equal)it ignores the first part
I added the Forecast tco2 and Actual tco2 fields to the query to see the values they are the same.
Any further thoughts

Thanks
CNEILL

 

[Remou]

Do these fields involve decimals based on calculations, if so, it is likely that you have run into a data type problem (

http://wiki.lessthandot.com/index.php/Simple_Maths,_Wrong_Answers).

Round may suit.

http://lessthandot.com