Results 1 to 6 of 6
  1. #1
    Join Date
    May 2012
    Posts
    5

    Question Unanswered: Filter query: show if Field_1<A OR Field_2>B

    Pretty much what the title says.

    I have a query showing a list of products that I put up on an auction house. I would like to show only those products which have been listed less than 5 times OR sell for a profit of more than $1000.

    Looking at it another way, I'd like to exclude products that have listed 5 or more times AND sell for a profit of less than $1000.

    I can apply filter criteria to individual fields just fine, but I can't figure out how to implement this compound filter using data from 2 different fields.

    Also, I'm a noob so please, if at all possible, no VBA.

    Thanks!

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    without knowing how you track how many times an items has been listed or even for what value it sold for its hard to say
    some details of the table design(s) would be nice
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    May 2012
    Posts
    5
    Thanks for replying!

    Explaining how the whole shebang is set up would take a while; maybe these screencaps will give you the necessary info? If not, let me know what else you need.

    The screencaps are of the query I want to filter. One shows it in datasheet view, and the other two in design view (I had to take two to be able to show all the columns.)

    As you can see, the query includes, among other things, Profit and Expirations for each individual Item. I would like to filter it so it excludes those items that have BOTH a profit of less than A, and Expirations greater than B.

    In other words, I would like to know how to show only those records which fulfill the criteria:

    Profit > A OR Expired < B

    Another way of going about it would be to exclude any records fulfilling:

    Profit < A AND Expired > B

    I hope this helps, and thanks in advance for taking the time to reply!
    Attached Thumbnails Attached Thumbnails Q1 - DS - 1.png   Q1 - D - 1.png   Q1 - D - 2.png  

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    sorry I was expecting a far more tricky problem with a normalised db

    Code:
    where profit >= 1000 or listing < 5
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    May 2012
    Posts
    5
    Thanks!

    Unfortunately, my noobishness is such that I don't know where to put that. I tried putting it at the end of the SQL statement (there was already the age condition, so I added this with an AND - can I do that?), but it prompts me to input 'Profit' when I try to run it. Is it because 'Profit' is a calculated field in this query, so I can't reference it? If so, does that mean I have to create a new query based off of this one, so that it's no longer a calculated field? Or maybe just use the formula for Profit instead of referencing it as a field?

    Thanks again!

  6. #6
    Join Date
    May 2012
    Posts
    5
    OK, I'm an idiot.

    I'm not sure where I got this idea, but for some reason I was sure this couldn't be done it the report itself (which is what I was ultimately trying to do). It can, though, and it's dead easy.

    Just in case there are others as braindead as me out there, all I had to do was enter the logical statement into the "Filter" box in the form's properties, and select "Yes" on "filter on load".

    Regardless, thanks again, healden, for taking the time to reply.

Tags for this Thread

Posting Permissions

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