Why Criteria will not filter zero?

[PNGBill]

Hi Forum,

I have a query including one field being a sum of balances (summed in earlier query).
To restrict the thousands of results I would like to filter zero balance records and leave just positive and negative balances.
If I use <0 the result is all zero and negative records and >0 gets all positve and zero balance records and <>0 gets all records including zero.

Workaround is <-0.001 or >0.001 which filters all records between neg 0.001 and positive 0.001.
Effectively this is just as good as filtering zero BUT why wouldn't the zero filter work??

 

[HiTechCoach]

Hi Forum,

I have a query including one field being a sum of balances (summed in earlier query).
To restrict the thousands of results I would like to filter zero balance records and leave just positive and negative balances.
If I use <0 the result is all zero and negative records and >0 gets all positve and zero balance records and <>0 gets all records including zero.

Workaround is <-0.001 or >0.001 which filters all records between neg 0.001 and positive 0.001.
Effectively this is just as good as filtering zero BUT why wouldn't the zero filter work??

if >0 include 0 the I bet they really are zero but only formated to display like that

to test this:
try finding all the records that =0

 

[pbaldy]

Two possibilities come immediately to mind. If the data type of the field is double or single, those are floating point numbers that can look like 0 but actually be things like .000000001. The solution would be to change the data type to currency. Another is that there are calculations that aren't being rounded, so while you may see 0 a different number might be stored (again, something like .00001). The solution to that would be to round the calculation.

 

[PNGBill]

Of the 7,000 records, 500 were filtered by not zero and some 5,000 odd were some 5 or more dec places into positive or negative less then 1 or -1
Will have to do some more house keeping but for now using criteria <-0.001or >0.001 does the job - Thanks

 

[Brianwarnock]

Why don't you use criteria Where Int(yourfield)<>0
or as it is a balance are you working to 2 decimal places?
Then Round(yourfield,2) <>0 , of course 0.006 will show as it would round to 0.01, but then that is correct.

Brian

 

[PNGBill]

Thanks Brian, Looking for accounts that owe money so a balance of .01 is effectively zero.
I guess <-1 or >1 would give the result I need.
Does Round(yourfield,2) really make the result 2 decimal places? I have used Int(([field]*100))/100 to get a result where truly only 2 dec places exist.
Most of our results are account balances and we do need a statement to add up and not be 1.00 plus 1.50 = 2.51
I will try Round - thanks