databarracks
asked on
Multiple 'OR' operator criteria for query
Hi there,
I would like to know what I am doing wrong with a particluar query that I am trying to filter multiple results based on form fields.
I have a table with 4 boolean fields and would like for my query to return the results only where the relevant form check boxes are true. If the criteria for that field is false I don't want it to return if corresponding check box on the form hasn't been ticked. For example if I tick txtBox1 and txtBox 2 on my form I only want to show records where Service 1 OR Service2 are true and so on. Please see code below:
SELECT tblCompany.company_id, tblCompany.company_name, tblCompany.cancelled, tblCompany.Service1, tblCompany. Service2, tblCompany. Service3, tblCompany. Service4
FROM tblCompany
WHERE (((tblCompany.company_id) Not Like 632 And (tblCompany.company_id) Not Like 633) AND ((tblCompany.cancelled)=Fa
ORDER BY tblCompany.company_name;
Many thanks for your help in advance
I would like to know what I am doing wrong with a particluar query that I am trying to filter multiple results based on form fields.
I have a table with 4 boolean fields and would like for my query to return the results only where the relevant form check boxes are true. If the criteria for that field is false I don't want it to return if corresponding check box on the form hasn't been ticked. For example if I tick txtBox1 and txtBox 2 on my form I only want to show records where Service 1 OR Service2 are true and so on. Please see code below:
SELECT tblCompany.company_id, tblCompany.company_name, tblCompany.cancelled, tblCompany.Service1, tblCompany. Service2, tblCompany. Service3, tblCompany. Service4
FROM tblCompany
WHERE (((tblCompany.company_id) Not Like 632 And (tblCompany.company_id) Not Like 633) AND ((tblCompany.cancelled)=Fa
ORDER BY tblCompany.company_name;
Many thanks for your help in advance
ASKER
Hi there,
I have tried that and it still just shows all the records?? Can't understand why it doesn't work?
I have tried that and it still just shows all the records?? Can't understand why it doesn't work?
You can also simplify this greatly by removing prefixes and unnecessary parentheses (you just need the parentheses to prioritize the ORs):
SELECT company_id, company_name, cancelled, Service1, Service2, Service3, Service4
FROM tblCompany
WHERE company_id Not Like 632 And company_id Not Like 633 AND tblCompany.cancelled=False
ORDER BY company_name;
SELECT company_id, company_name, cancelled, Service1, Service2, Service3, Service4
FROM tblCompany
WHERE company_id Not Like 632 And company_id Not Like 633 AND tblCompany.cancelled=False
ORDER BY company_name;
ASKER
I have removed the unecessary prefixes etc. but still not filtering te query still. I cannot see where it is going wrong. I do appreciate your help on this matter and if you can suggest another way of creating this query it would be greatly appreciated.
I thogh of using query defs in my vba code but that would be messy considering that the OR criteria would be changin depending on the choices made on the form?
I thogh of using query defs in my vba code but that would be messy considering that the OR criteria would be changin depending on the choices made on the form?
Does this return the expected results for just the first two checkboxes?
SELECT company_id, company_name, cancelled, Service1, Service2, Service3, Service4
FROM tblCompany
WHERE company_id Not Like 632 And company_id Not Like 633 AND tblCompany.cancelled=False
ORDER BY company_name;
SELECT company_id, company_name, cancelled, Service1, Service2, Service3, Service4
FROM tblCompany
WHERE company_id Not Like 632 And company_id Not Like 633 AND tblCompany.cancelled=False
ORDER BY company_name;
ASKER
Yes if I select the two leftmost boxes it works but only if both boxes are ticked if one isnt it shows all
ASKER
Sorry I meant that it works better when there are only two boxes to filter but if I only tick one of them on the form it show all of them. If both are ticked then it works accordingly.
Apologies for the previous message
Apologies for the previous message
Are you using Access?
I think you might need to use a VBA function for this.
What are you using your query for (recordsource of form/report or something else)?
Lets see what others come up with. I'll help out with a vba function if there are no other responses here in a couple of hours.
I think you might need to use a VBA function for this.
What are you using your query for (recordsource of form/report or something else)?
Lets see what others come up with. I'll help out with a vba function if there are no other responses here in a couple of hours.
ASKER
Yes I am using ms access. True I thought a VBA function might be the best way forward, perhaps a query defs or something like that, to alter the query depending on the choices made by the user.
My only worry was how to change the sql syntax from vba because there could be a multitude of variants when choosing the criteria.
My only worry was how to change the sql syntax from vba because there could be a multitude of variants when choosing the criteria.
How is the query's result set being used?
I have a meeting coming up but will check back later today.
I have a meeting coming up but will check back later today.
ASKER
The result set will be used for a mail merge loop for people who belong in the selected services. So the user can select recipients that have either service 1 or service 2 or service 3 or service 4 or all.
As an example, they should not be viewing any recipients that don't have the service ticked on the main form.
As an example, they should not be viewing any recipients that don't have the service ticked on the main form.
ASKER
Hi there I think I figures out eventually. I used expressions for each field using an iif statement and if result field is false I would declare it as null and therefore allowing me to filter criteria as initially intended.
Good job!
If you post the iifs that you used, you can close this question by accepting your own comment.
If you post the iifs that you used, you can close this question by accepting your own comment.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I had a lot of help from the expert but through sheer trial and error I was able to make it work via iif statements. Thank you to the expert for guiding me in the right direction.
SELECT tblCompany.company_id, tblCompany.company_name, tblCompany.cancelled, tblCompany.Service1, tblCompany. Service2, tblCompany. Service3, tblCompany. Service4
FROM tblCompany
WHERE (((tblCompany.company_id) Not Like 632 And (tblCompany.company_id) Not Like 633) AND ((tblCompany.cancelled)=Fa
ORDER BY tblCompany.company_name;