Unanswered: Multiple Criteria Using Combo Boxes - How to Switch between AND/OR
Hey everyone -
Say I want to use more than one combo box. I am having a difficult time defining AND/OR functions. For exapmple: one owner may have many accounts. i want to specify the owner and show all acounts, then narrow down by another criteria, and so on and so forth; however I also want the user to be able to leave a given field blank if they so choose. Right now a blank combo box is finding null values and no matches are appearing because it is based off the AND function (Owner AND null account). But if I use the OR function it doesnt drill far enough down (owner - all owner accounts OR blank accounts - so all the accounts still appear).
This seems like it should be really easy but it just isn't working out for me.
Originally posted by kire nievs
If you use the combos as criteria in a query I think you can let the null value give you all records by typing --Forms!YourForm!cboName Or Forms!YourForm!cboName Is Null-- in the criteria field.
I've created a test file that is overly simple. I have a table of Account Information. I have a query that uses the criteria of Owner and Reviewer from two combo boxes. I have a form with the two combo boxes on it. I then have a subform that displays the data based on the criteria set in the combo boxes.
Owner has a one to many relationship with reviewer. So by choosing owner i want to view all the accounts associated with that owner. So I see that one owner has two reviewers.
I then select reviewer. The same number of records remain because the criteria is set to Owner OR Reviewer. It is here that I need the AND function.
If I select an owner and leave reviewer as null. No records are returned because there are no null values for reviewer. It is here that I need OR. If I select AND reviewer and there is a reviewer then it works fine because I want to see Owner and Reviewer.
I know this is a log winded explanation and I probably made no sense at all but I basically wqant to say:
If Owner IsNull than just use Reviewer. If Reviewer IsNull than just use Owner. Otherwise use Owner AND Reviewer. If both are null than provide no data.