Results 1 to 4 of 4
  1. #1
    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. #2
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    Quote 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. #3
    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. #4
    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
  •