Link to home
Start Free TrialLog in
Avatar of urjudo
urjudoFlag 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,
Avatar of urjudo
urjudo
Flag of United States of America image

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));
Avatar of 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
Avatar of Hamed Nasr
Hamed Nasr
Flag of Oman image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of 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.
Try to upload a sample database, with only 2 check boxes and one combo in the where clause. Reduce the number of output fields.
Avatar of 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
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?
Avatar of 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.
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?
Avatar of 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.
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.
Avatar of 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
Avatar of 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.
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
Avatar of 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
Welcome!