Avatar of urjudo
urjudo
Flag for United States of America asked on

and/or condition in Querry

Hi Experts,
I'm working on a query, I have a combo box in a form, here is my query, what I want to do is: I want user to be able to search either they select data from the combo box or not.  If they don't select the combo box then query will show all the records that they checked, if the select from the combo box then show all the records that match all the check boxes plus the combo box.  Do I need to have two queries? I would like to use one query.  below is my query:

SELECT DISTINCT tblBriefBank.BBID, tblBriefBank.BBCase, tblBriefBank.BBDocket, tblBriefBank.BBChildDOB, tblBriefBank.BBPAtty, tblBriefBank.BBPAttyFirm, tblBriefBank.BBMTPD, tblBriefBank.BBDispostition, tblBriefBank.BBJudge, tblJudge.JDName, tblBriefBank.BBDateRuling, tblBriefBank.BBTranscript, tblBriefBank.BBAppBrief, tblBriefBank.BBAppDescision, tblBriefBank.BBIRA, tblBriefBank.BBIRB, tblBriefBank.BBIRC, tblBriefBank.BBIRD, tblBriefBank.BBIRE, tblBriefBank.BBIRF, tblBriefBank.BBIRG, tblBriefBank.BBIRH, tblBriefBank.BBIRI, tblBriefBank.BBIRJ, tblBriefBank.BBIRK, tblBriefBank.BBIRL
FROM tblBriefBank INNER JOIN tblJudge ON tblBriefBank.BBJudge = tblJudge.JDCode
WHERE (((tblBriefBank.BBMTPD)=[forms]![frmASAInquiry]![Text135]) AND ((tblBriefBank.BBIRA)=[forms]![frmASAInquiry]![BBIRA]) AND ((tblBriefBank.BBIRB)=[forms]![frmASAInquiry]![BBIRB]) AND ((tblBriefBank.BBIRC)=[forms]![frmASAInquiry]![BBIRC]) AND ((tblBriefBank.BBIRD)=[forms]![frmASAInquiry]![BBIRD]) AND ((tblBriefBank.BBIRE)=[forms]![frmASAInquiry]![BBIRE]) AND ((tblBriefBank.BBIRF)=[forms]![frmASAInquiry]![BBIRF]) AND ((tblBriefBank.BBIRG)=[forms]![frmASAInquiry]![BBIRG]) AND ((tblBriefBank.BBIRH)=[forms]![frmASAInquiry]![BBIRH]) AND ((tblBriefBank.BBIRI)=[forms]![frmASAInquiry]![BBIRI]) AND ((tblBriefBank.BBIRJ)=[forms]![frmASAInquiry]![BBIRJ]) AND ((tblBriefBank.BBIRK)=[forms]![frmASAInquiry]![BBIRK]) AND ((tblBriefBank.BBIRL)=[forms]![frmASAInquiry]![BBIRL])) OR (((tblBriefBank.BBJudge)=[forms]![frmASAInquiry]![Combo140]));

This query shows all the records but if I select from the combo box then it's not working, I know it needs to be AND and OR, but could not figure it out how.

Thanks,
Microsoft Access

Avatar of undefined
Last Comment
Hamed Nasr

8/22/2022 - Mon
urjudo

ASKER
Sorry, here is the one I tried

SELECT DISTINCT tblBriefBank.BBID, tblBriefBank.BBCase, tblBriefBank.BBDocket, tblBriefBank.BBChildDOB, tblBriefBank.BBPAtty, tblBriefBank.BBPAttyFirm, tblBriefBank.BBMTPD, tblBriefBank.BBDispostition, tblBriefBank.BBJudge, tblJudge.JDName, tblBriefBank.BBDateRuling, tblBriefBank.BBTranscript, tblBriefBank.BBAppBrief, tblBriefBank.BBAppDescision, tblBriefBank.BBIRA, tblBriefBank.BBIRB, tblBriefBank.BBIRC, tblBriefBank.BBIRD, tblBriefBank.BBIRE, tblBriefBank.BBIRF, tblBriefBank.BBIRG, tblBriefBank.BBIRH, tblBriefBank.BBIRI, tblBriefBank.BBIRJ, tblBriefBank.BBIRK, tblBriefBank.BBIRL
FROM tblBriefBank INNER JOIN tblJudge ON tblBriefBank.BBJudge = tblJudge.JDCode
WHERE (((tblBriefBank.BBMTPD)=forms!frmASAInquiry!Text135) And ((tblBriefBank.BBIRA)=forms!frmASAInquiry!BBIRA) And ((tblBriefBank.BBIRB)=forms!frmASAInquiry!BBIRB) And ((tblBriefBank.BBIRC)=forms!frmASAInquiry!BBIRC) And ((tblBriefBank.BBIRD)=forms!frmASAInquiry!BBIRD) And ((tblBriefBank.BBIRE)=forms!frmASAInquiry!BBIRE) And ((tblBriefBank.BBIRF)=forms!frmASAInquiry!BBIRF) And ((tblBriefBank.BBIRG)=forms!frmASAInquiry!BBIRG) And ((tblBriefBank.BBIRH)=forms!frmASAInquiry!BBIRH) And ((tblBriefBank.BBIRI)=forms!frmASAInquiry!BBIRI) And ((tblBriefBank.BBIRJ)=forms!frmASAInquiry!BBIRJ) And ((tblBriefBank.BBIRK)=forms!frmASAInquiry!BBIRK) And ((tblBriefBank.BBIRL)=forms!frmASAInquiry!BBIRL)) Or (((tblBriefBank.BBMTPD)=forms!frmASAInquiry!Text135) And ((tblBriefBank.BBJudge)=forms!frmASAInquiry!Combo140) And ((tblBriefBank.BBIRA)=forms!frmASAInquiry!BBIRA) And ((tblBriefBank.BBIRB)=forms!frmASAInquiry!BBIRB) And ((tblBriefBank.BBIRC)=forms!frmASAInquiry!BBIRC) And ((tblBriefBank.BBIRD)=forms!frmASAInquiry!BBIRD) And ((tblBriefBank.BBIRE)=forms!frmASAInquiry!BBIRE) And ((tblBriefBank.BBIRF)=forms!frmASAInquiry!BBIRF) And ((tblBriefBank.BBIRG)=forms!frmASAInquiry!BBIRG) And ((tblBriefBank.BBIRH)=forms!frmASAInquiry!BBIRH) And ((tblBriefBank.BBIRI)=forms!frmASAInquiry!BBIRI) And ((tblBriefBank.BBIRJ)=forms!frmASAInquiry!BBIRJ) And ((tblBriefBank.BBIRK)=forms!frmASAInquiry!BBIRK) And ((tblBriefBank.BBIRL)=forms!frmASAInquiry!BBIRL));
Sharath S

try this
SELECT DISTINCT tblBriefBank.BBID, 
                tblBriefBank.BBCase, 
                tblBriefBank.BBDocket, 
                tblBriefBank.BBChildDOB, 
                tblBriefBank.BBPAtty, 
                tblBriefBank.BBPAttyFirm, 
                tblBriefBank.BBMTPD, 
                tblBriefBank.BBDispostition, 
                tblBriefBank.BBJudge, 
                tblJudge.JDName, 
                tblBriefBank.BBDateRuling, 
                tblBriefBank.BBTranscript, 
                tblBriefBank.BBAppBrief, 
                tblBriefBank.BBAppDescision, 
                tblBriefBank.BBIRA, 
                tblBriefBank.BBIRB, 
                tblBriefBank.BBIRC, 
                tblBriefBank.BBIRD, 
                tblBriefBank.BBIRE, 
                tblBriefBank.BBIRF, 
                tblBriefBank.BBIRG, 
                tblBriefBank.BBIRH, 
                tblBriefBank.BBIRI, 
                tblBriefBank.BBIRJ, 
                tblBriefBank.BBIRK, 
                tblBriefBank.BBIRL 
  FROM tblBriefBank 
       INNER JOIN tblJudge 
               ON tblBriefBank.BBJudge = tblJudge.JDCode 
 WHERE ( ( ( tblBriefBank.BBMTPD ) = NZ((forms ! frmASAInquiry ! Text135 ),tblBriefBank.BBMTPD) 
         AND ( ( tblBriefBank.BBJudge ) = forms ! frmASAInquiry ! Combo140 ) 
         AND ( ( tblBriefBank.BBIRA ) = forms ! frmASAInquiry ! BBIRA ) 
         AND ( ( tblBriefBank.BBIRB ) = forms ! frmASAInquiry ! BBIRB ) 
         AND ( ( tblBriefBank.BBIRC ) = forms ! frmASAInquiry ! BBIRC ) 
         AND ( ( tblBriefBank.BBIRD ) = forms ! frmASAInquiry ! BBIRD ) 
         AND ( ( tblBriefBank.BBIRE ) = forms ! frmASAInquiry ! BBIRE ) 
         AND ( ( tblBriefBank.BBIRF ) = forms ! frmASAInquiry ! BBIRF ) 
         AND ( ( tblBriefBank.BBIRG ) = forms ! frmASAInquiry ! BBIRG ) 
         AND ( ( tblBriefBank.BBIRH ) = forms ! frmASAInquiry ! BBIRH ) 
         AND ( ( tblBriefBank.BBIRI ) = forms ! frmASAInquiry ! BBIRI ) 
         AND ( ( tblBriefBank.BBIRJ ) = forms ! frmASAInquiry ! BBIRJ ) 
         AND ( ( tblBriefBank.BBIRK ) = forms ! frmASAInquiry ! BBIRK ) 
         AND ( ( tblBriefBank.BBIRL ) = forms ! frmASAInquiry ! BBIRL ) ); 

Open in new window

ASKER CERTIFIED SOLUTION
Hamed Nasr

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
urjudo

ASKER
Sorry, I tried both suggestions, it seems still not working,  I know the problem is in the Combo box, it needs to be AND or Or of the combo box.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Hamed Nasr

Try to upload a sample database, with only 2 check boxes and one combo in the where clause. Reduce the number of output fields.
urjudo

ASKER
here is the DB.  It's on the ASA Inquiry Form, on the form you can check the first two check boxes on the left side and the first check box on the right side, it will show you two records, one with Judge Test1 and one with Judge test2, what I want to do is when user see both records, if they want to see only Judge Test1 then they check the Judge and select Test1 then when you click search again it will requery and only shows the record(s) that has Judge Test1 on it.    Thanks
MyDB.mdb
Hamed Nasr

More info:
Selecting BBIRA, B, and G checks displays 2 records.
Why removing G displays nothing. And Adding H displays nothing.
What is the logic behind checking boxes?
What do you expect if you check all boxes from A to F?
What do you expect if you check all boxes from A to L?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
urjudo

ASKER
I tried to get the records that matches what ever the user check in the check boxes, the records are base on the Case Update form that user enter, different case has different criteria, so if user wants to look up the case(s) that they check in the Inquiry form, then shows the record(s) match they checked in the Inquiry form, that part is working, but when user want s to limited the case(s) base on the Judge's name, that's what I'm having problem.
Hamed Nasr

Please answer my queries. What you explained added more confusion. The query does not make sense as it is. By clearing the misunderstanding above, it might be simplified.
More info:
Selecting BBIRA, B, and G checks displays 2 records.
Why removing G displays nothing?
       And Adding H displays nothing?
What is the logic behind checking boxes?
What do you expect if you check all boxes from A to F?
What do you expect if you check all boxes from A to L?
urjudo

ASKER
because it the tblBriefBank, there are 2 records that have BBIRA, B and G checked.  that's why it show two records when you checked BBIRA, B & G, if you checkBBIRA, B, G & H, it will not have any records that is check in BBIRA, B, G & H in the tblBriefBank.  If we add a record from Case Update that all the ehceck boxes are checked that when you do the search in the ASAInquiry form for that case then you check all the check boxes on that form, it will shows the records.  The subformon the ASAInquiryForm is attach to the qryASAInquirySub.  
Your questions:
Why removing G displays nothing? -  because there is not record(s) that have BBIRG checked.
Adding H displays nothing? - because there is not record(s) that have BBIRH checked
The check boxes on the ASA Inquiry Form are exactly as the check boxes in the Case update form
What is the logic behind checking boxes? - for users who want to look up the case that match the criteria that they check in the ASAInquiry Form.
What do you expect if you check all boxes from A to F?  If there is a case or cases that enter in the Case Update form are got check from A to F then show in the subform on the ASAInquiryForm  same as your last question.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Hamed Nasr

tblBriefBank has 2 records with these checks
BBIRA      BBIRB      BBIRC      BBIRD      BBIRE      BBIRF      BBIRG
True        True       False       False       False       False      True
True        True       False       False       False       False      True

So the only 2 records displayed when you check A, B, and G in AS Inquiry form.
These can be updated using Case update form.

So my understanding to the search form is like this:
Assume the records for A, B, C as follows
id A  B  C
1  T   T  T
2  T   T  F
3  T   F  F
4  F   F  F.

In search form checking A , I expect 1, 2, 3
Checking A, B: I expect 1, 2
Checking A, B, C: I expect 1

Then comes the combo if not selected use the ones from checks, otherwise filter more using the combo.

If this does not make sense then, sorry, I have to leave it for someone to take it from here.
urjudo

ASKER
You're absolutly right, that's what I trying to  do is what you said "Then comes the combo if not selected use the ones from checks, otherwise filter more using the combo." but could figure out the second part which is "otherwise filter more using the combo."  thanks
urjudo

ASKER
I should say that's what I stuck in the qryASAUnquirySub on the section of the BBJudge, I could not figure out how to put the AND & OR condition on that field.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Hamed Nasr

Try this: Modifications made for form in current event, search button, check boxes A, B and C
You need to expand for other check boxes.
Clear check boxes
Combo box.

Hope this effort will be helpful.
MyDB-Q-28079102--2----Copy.mdb
urjudo

ASKER
Hi hnasr,
I figure it out the way to work that I was loking for. I put Like ([forms]![frmASAInquiry]![Combo140] & "*") in the BBJudge field in the query.
Thanks for your help and your time, I will still give you the full point for your effort.

thank again
Hamed Nasr

Welcome!
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy