urjudo
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.BBDispostitio n, tblBriefBank.BBJudge, tblJudge.JDName, tblBriefBank.BBDateRuling, tblBriefBank.BBTranscript, tblBriefBank.BBAppBrief, tblBriefBank.BBAppDescisio n, 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)=[f orms]![frm ASAInquiry ]![Text135 ]) AND ((tblBriefBank.BBIRA)=[for ms]![frmAS AInquiry]! [BBIRA]) AND ((tblBriefBank.BBIRB)=[for ms]![frmAS AInquiry]! [BBIRB]) AND ((tblBriefBank.BBIRC)=[for ms]![frmAS AInquiry]! [BBIRC]) AND ((tblBriefBank.BBIRD)=[for ms]![frmAS AInquiry]! [BBIRD]) AND ((tblBriefBank.BBIRE)=[for ms]![frmAS AInquiry]! [BBIRE]) AND ((tblBriefBank.BBIRF)=[for ms]![frmAS AInquiry]! [BBIRF]) AND ((tblBriefBank.BBIRG)=[for ms]![frmAS AInquiry]! [BBIRG]) AND ((tblBriefBank.BBIRH)=[for ms]![frmAS AInquiry]! [BBIRH]) AND ((tblBriefBank.BBIRI)=[for ms]![frmAS AInquiry]! [BBIRI]) AND ((tblBriefBank.BBIRJ)=[for ms]![frmAS AInquiry]! [BBIRJ]) AND ((tblBriefBank.BBIRK)=[for ms]![frmAS AInquiry]! [BBIRK]) AND ((tblBriefBank.BBIRL)=[for ms]![frmAS AInquiry]! [BBIRL])) OR (((tblBriefBank.BBJudge)=[ forms]![fr mASAInquir y]![Combo1 40]));
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,
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.BBDispostitio
FROM tblBriefBank INNER JOIN tblJudge ON tblBriefBank.BBJudge = tblJudge.JDCode
WHERE (((tblBriefBank.BBMTPD)=[f
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,
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 ) );
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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
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?
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?
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?
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?
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.
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.
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.
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
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
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
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
I figure it out the way to work that I was loking for. I put Like ([forms]![frmASAInquiry]![
Thanks for your help and your time, I will still give you the full point for your effort.
thank again
Welcome!
ASKER
SELECT DISTINCT tblBriefBank.BBID, tblBriefBank.BBCase, tblBriefBank.BBDocket, tblBriefBank.BBChildDOB, tblBriefBank.BBPAtty, tblBriefBank.BBPAttyFirm, tblBriefBank.BBMTPD, tblBriefBank.BBDispostitio
FROM tblBriefBank INNER JOIN tblJudge ON tblBriefBank.BBJudge = tblJudge.JDCode
WHERE (((tblBriefBank.BBMTPD)=fo