# Thread: Which criteria operator would do this?

1. Registered User
Join Date
May 2004
Posts
3

## Unanswered: Which criteria operator would do this?

Say I have a query with data that looks like this

Case__Bill____Pay____Outstanding
1_____20_____0______20
2_____0______20______0
3_____40_____40______0
4_____0______0______35
5_____0______0_______0

What I want to do is to show the entire row if there is data (other than 0) in any of the columns Bill, Pay, or Outstanding. (i.e., case 5 would get trimmed). Or in other words, ignore a case with only 0’s but show it if there is data in any single field. Is there an easy way to do this?

Right now, every operator trims the data to much :-(

(in this example, data for Bill and Pay are from queries for the current month while Outstanding is calculated from all months)

Thanks again,
Euthymic

2. Registered User
Join Date
Apr 2004
Location
Sydney Australia
Posts
369
Originally Posted by euthymic
Say I have a query with data that looks like this

Case__Bill____Pay____Outstanding
1_____20_____0______20
2_____0______20______0
3_____40_____40______0
4_____0______0______35
5_____0______0_______0

What I want to do is to show the entire row if there is data (other than 0) in any of the columns Bill, Pay, or Outstanding. (i.e., case 5 would get trimmed). Or in other words, ignore a case with only 0’s but show it if there is data in any single field. Is there an easy way to do this?

Right now, every operator trims the data to h :-(

(in this example, data for Bill and Pay are from queries for the current month while Outstanding is calculated from all months)

Thanks again,
Euthymic
I am assuming you mean by "trims the data" that records are not showing in the query.

If place something in the criteria for each field and on the same criteria line what you have is an And situation. For example, if you had >0 for each field on the same criteria line then only records that had >0 in in each field would be displayed. In the example you have uses then a >0 on the same criteria line for each field would not have displayed any of the records in your example.

If you place the criteria on a different criteria line for each field then it becomes an Or situation.

Sometimes the best way to handle these situations is to create another field and base your selection on that field.

If I am reading your posting correctly is seems that you want all records displayed that have an entry of 1 or more in at least one of the three fields. If that is the case then you could creater another field like:

NewField:[Bill]+[Pay]+[Outstanding]

You would now select records on that field that are >0

Mike

3. Registered User
Join Date
May 2004
Posts
3
Mike375,

If I was in Australia I would buy you a beer

As a newbie in the land of databases, your post jogged me to try something that I hadn’t grasped while reading through the books. I had no idea you could keep adding criteria to the empty spaces below the “Criteria” and “Or” headers at the left (It doesn’t help that my design view defaults to showing only those 2 rows).

I’ll try your other suggestion as well but I sure am happy I’ll be able to get some sleep tonight!!!

Thanks again,

-euthymic

4. Registered User
Join Date
Apr 2004
Location
Sydney Australia
Posts
369
(It doesn’t help that my design view defaults to showing only those 2 rows).

In my Access 95 there are 9 rows shown for criteria in Query design.

One other thing I should have mentioned is that for

NewField:[Bill]+[Pay]+[Outstanding]

to work each of the fields in question should be a number field. You may need to go back to your table and check that and if they are Text field then change them to number fields.

Sleep well. 11pm Thursday on the US East Coast and lunch time Friday in Sydney Australia

Mike

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•