Exclude Multiple Zeros in Query

[alecgreen]

Hi

I my Query I have 5 calculated fields in 5 columns in the grid.

Each of the calculated in a Currency value: value1, value2, value3,
value4 and value5.

I want to exclude any results that are zero in all 5 fields,

but not if any value greater than zero exists in any of the 5 fields.

Mant Thanks

Alec

 

[John Spencer]

You would use a where clause like the following in the query.

Where Value1 <> 0 and Value2<>0 and Value3 <> 0 and Value4<> and Value5 <> 0

If you are using query design view (the grid) then enter <>0 as the criteria
under each of the calculated columns.


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[Bob Barrows]

John,
I don't think that meets alec's requirements. Your suggestion will reject a
record if any of the values = 0. He only wants to reject the record if all
the values are zero. So it needs to be something like this:
WHERE NOT (Value1 = 0 and Value2=0 and Value3 = 0 and Value4=0 and Value5 =
0)
This is most easily done in the SQL View, but can be done in the design grid
by entering this expression:
(Value1 = 0 and Value2=0 and Value3 = 0 and Value4=0 and Value5 = 0)
into the top row of a new column and entering False into the criteria row.

Alternatively, if none of the values can be negative, alec can add a
criteria that calculates the sum of those 5 columns and restricts the
results to those rows where that sum is greater than zero. Again, this is
most easily done in SQL view, but it can be done in the grid by entering the
expression that sums the values in a new column, using >0 in the criteria
row.

Of course, if negative values can exist, the sum of the values can be zero
even when non-zero values exist (0,-1,1,0,0) so you need to use my
modification of John's solution.

 

[John Spencer]

Of course, you are correct.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[alecgreen]

Of course, you are correct.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County






- Show quoted text -

Many Thanks for your help guys