Query not filtering correctly, 0 values still showing

[Jaye7]

I have a calculated field which is based on one field less another.

In the calculated field I put < > 0, yet it keeps returning zero's.
I then put >1 And < -1 so if there are decimals affecting the results.
Still zeros are shown.
I then put the decimals to 10 places and none show as being any different than 0.000000000.

I then make another query which just looks at the other query's calculated field and then I get only those greater than or less than zero, which is what I want

Am I missing something.

I don't want to have to base one query on another to get the desired results, is there a way to get the desired results in that field.

I have tried, NOT 0, tried text < > "0" and anything else that I can think of to no avail.

 

[homage]

Are they null values? Maybe try <>0 or is not null

 

[Jaye7]

No, they are not null, they show as zeros.

 

[jdraw]

Did you try taking the space out of this < >? Perhaps you could show us the code involved.

 

[Jaye7]

I found a way around using another query.

I added another field with the following and it is working.

Balance1: IIf([Balance]<>0,"TRUE","FALSE")

 

[Jaye7]

jdraw, it didn't have a space in the actual query criteria, I just added it in the thread as it looked strange without a space, I thought that someone might pull me up on it.

 

[jdraw]

Glad you have it resolved.

 

[spikepl]

When you have variables of type Single or Double, teh valeus are not represented 100 % exact. What you see on screeen is a formatted display,not necessarily reflecting the actual value held. TO compare two calculated values of type Double:
A=B wil ahrdly ever give the the desired result, since there is loways "fluff" opn last devimals, that you may or may not see.

To determine if two Double numbers are equal within the desired precisionm the proper test is:

Abs(A-B)< eps, where eps is a very small value you set

For financials you can however use datatype Currency, which is precise to 4 decimals.