Using AND in Queries

  • Thread starter Thread starter greg.kylelangley
  • Start date Start date
G

greg.kylelangley

[Sorry to re-post this, but was offered one solution before by
Vanderghast a while ago telling me to look at
http://www.mvps.org/access/queries/qry0016.htm, but that didn't seem to
do exactly what I wanted (or I couldn't implement it!). Wondered if
anyone had any new ideas.]


I've a database detailing various suppliers [tblSuppliers] and the
things they can do [tblCapabilities]. These are linked by
[tblSupplierCapabilityLink].

I currently have a working search form [frmSearchCapability] allowing
the user to select up to four capabilities from four combo boxes. The
form then updates a list box with all the companies that have ANY ONE
of these capabilities - an OR search.


I need also to provide an AND search - so only the companies that have
ALL the specified capabilities are returned. I'm happy with the results

of this appearing in a different list box, or even a different form,
but I can't get the search to work. The OR search currently has the
following criteria:


[Forms]![frmSearchCapability]![comboCapability] Or
[Forms]![frmSearchCapability]![comboCapability2] Or
[Forms]![frmSearchCapability]![comboCapability3] Or
[Forms]![frmSearchCapability]![comboCapability4].


How can I get the AND search to work in a similar way, ignoring Null
responses?


Any help -very- gratefully received!


Greg
 
This would require a subquery in the where clause

Assumption:
tblSupplierCapabilityLink has a maximum of one record per combination of
Supplier and Capability

SELECT tblSuppliers.*
FROM tblSuppliers
WHERE TblSuppliers.SupplierID IN (
Select T.SupplierID
FROM TblSupplierCapabilityLink as T
WHERE T.CapabilityID = [Forms]![frmSearchCapability]![comboCapability] Or
T.CapabilityID = [Forms]![frmSearchCapability]![comboCapability2] Or
T.CapabilityID =[Forms]![frmSearchCapability]![comboCapability3] Or
T.CapabilityID = [Forms]![frmSearchCapability]![comboCapability4]
GROUP BY T.SupplierID
HAVING COUNT(*) = IIF([Forms]![frmSearchCapability]![comboCapability] is
Null,0,1) +
IIF([Forms]![frmSearchCapability]![comboCapability2] is Null,0,1) +
IIF([Forms]![frmSearchCapability]![comboCapability3] is Null,0,1) +
IIF([Forms]![frmSearchCapability]![comboCapability4] is Null,0,1))
 
I've a database detailing various suppliers [tblSuppliers] and the
things they can do [tblCapabilities]. These are linked by
[tblSupplierCapabilityLink].

I currently have a working search form [frmSearchCapability] allowing
the user to select up to four capabilities from four combo boxes. The
form then updates a list box with all the companies that have ANY ONE
of these capabilities - an OR search.

I need also to provide an AND search - so only the companies that have
ALL the specified capabilities are returned. I'm happy with the results
of this appearing in a different list box, or even a different form,
but I can't get the search to work. The OR search currently has the
following criteria:

[Forms]![frmSearchCapability]![comboCapability] Or
[Forms]![frmSearchCapability]![comboCapability2] Or
[Forms]![frmSearchCapability]![comboCapability3] Or
[Forms]![frmSearchCapability]![comboCapability4].

How can I get the AND search to work in a similar way, ignoring Null
responses?
Click to expand...

How about this:
IIF(Len([Forms]![frmSearchCapability]![comboCapability])<>0,
[Forms]![frmSearchCapability]![comboCapability], True) AND
IIF(Len([Forms]![frmSearchCapability]![comboCapability2])<>0,
[Forms]![frmSearchCapability]![comboCapability2], True) AND
IIF(Len([Forms]![frmSearchCapability]![comboCapability3])<>0,
[Forms]![frmSearchCapability]![comboCapability3], True) AND
IIF(Len([Forms]![frmSearchCapability]![comboCapability4])<>0,
[Forms]![frmSearchCapability]![comboCapability4], True) AND
 
Neil,

Thanks, but where do I put these criteria? Just putting them into the
query brings up the 'expression is too complex to be evaluated' error
when I open the form displaying the results...

GKL

Neil said:
I've a database detailing various suppliers [tblSuppliers] and the
things they can do [tblCapabilities]. These are linked by
[tblSupplierCapabilityLink].

I currently have a working search form [frmSearchCapability] allowing
the user to select up to four capabilities from four combo boxes. The
form then updates a list box with all the companies that have ANY ONE
of these capabilities - an OR search.

I need also to provide an AND search - so only the companies that have
ALL the specified capabilities are returned. I'm happy with the results
of this appearing in a different list box, or even a different form,
but I can't get the search to work. The OR search currently has the
following criteria:

[Forms]![frmSearchCapability]![comboCapability] Or
[Forms]![frmSearchCapability]![comboCapability2] Or
[Forms]![frmSearchCapability]![comboCapability3] Or
[Forms]![frmSearchCapability]![comboCapability4].

How can I get the AND search to work in a similar way, ignoring Null
responses?
Click to expand...

How about this:
IIF(Len([Forms]![frmSearchCapability]![comboCapability])<>0,
[Forms]![frmSearchCapability]![comboCapability], True) AND
IIF(Len([Forms]![frmSearchCapability]![comboCapability2])<>0,
[Forms]![frmSearchCapability]![comboCapability2], True) AND
IIF(Len([Forms]![frmSearchCapability]![comboCapability3])<>0,
[Forms]![frmSearchCapability]![comboCapability3], True) AND
IIF(Len([Forms]![frmSearchCapability]![comboCapability4])<>0,
[Forms]![frmSearchCapability]![comboCapability4], True) AND

--
Neil Sunderland
Braunton, Devon

Please observe the Reply-To address
Click to expand...
 
John,

Thanks, but I'm afraid I'm still not too hot on the old Access stuff -
where does this go?

Regards,

GKL


John said:
This would require a subquery in the where clause

Assumption:
tblSupplierCapabilityLink has a maximum of one record per combination of
Supplier and Capability

SELECT tblSuppliers.*
FROM tblSuppliers
WHERE TblSuppliers.SupplierID IN (
Select T.SupplierID
FROM TblSupplierCapabilityLink as T
WHERE T.CapabilityID = [Forms]![frmSearchCapability]![comboCapability] Or
T.CapabilityID = [Forms]![frmSearchCapability]![comboCapability2] Or
T.CapabilityID =[Forms]![frmSearchCapability]![comboCapability3] Or
T.CapabilityID = [Forms]![frmSearchCapability]![comboCapability4]
GROUP BY T.SupplierID
HAVING COUNT(*) = IIF([Forms]![frmSearchCapability]![comboCapability] is
Null,0,1) +
IIF([Forms]![frmSearchCapability]![comboCapability2] is Null,0,1) +
IIF([Forms]![frmSearchCapability]![comboCapability3] is Null,0,1) +
IIF([Forms]![frmSearchCapability]![comboCapability4] is Null,0,1))



[Sorry to re-post this, but was offered one solution before by
Vanderghast a while ago telling me to look at
http://www.mvps.org/access/queries/qry0016.htm, but that didn't seem to
do exactly what I wanted (or I couldn't implement it!). Wondered if
anyone had any new ideas.]


I've a database detailing various suppliers [tblSuppliers] and the
things they can do [tblCapabilities]. These are linked by
[tblSupplierCapabilityLink].

I currently have a working search form [frmSearchCapability] allowing
the user to select up to four capabilities from four combo boxes. The
form then updates a list box with all the companies that have ANY ONE
of these capabilities - an OR search.


I need also to provide an AND search - so only the companies that have
ALL the specified capabilities are returned. I'm happy with the results

of this appearing in a different list box, or even a different form,
but I can't get the search to work. The OR search currently has the
following criteria:


[Forms]![frmSearchCapability]![comboCapability] Or
[Forms]![frmSearchCapability]![comboCapability2] Or
[Forms]![frmSearchCapability]![comboCapability3] Or
[Forms]![frmSearchCapability]![comboCapability4].


How can I get the AND search to work in a similar way, ignoring Null
responses?


Any help -very- gratefully received!


Greg
Click to expand...
Click to expand...
 
That is a complete query and would be pasted into the SQL view of a query.
Open a new query, don't add any tables to it, use the menu View: SQL item to
switch to the text view. Paste the code into the sql view.
Try running the query.

IF that fails, then I've bollixed up the SQL somehow. You should get a
syntax error message of some type.

Another way to do this is in a COPY of your current query. Put the
following in the criteria under the Supplierid field

The criteria is all in one line, but to make it more readable, I've broken
it into multiple lines
Criteria: IN (Select T.SupplierID
FROM TblSupplierCapabilityLink as T
WHERE T.CapabilityID = [Forms]![frmSearchCapability]![comboCapability] Or
T.CapabilityID = [Forms]![frmSearchCapability]![comboCapability2] Or
T.CapabilityID =[Forms]![frmSearchCapability]![comboCapability3] Or
T.CapabilityID = [Forms]![frmSearchCapability]![comboCapability4]
GROUP BY T.SupplierID
HAVING COUNT(*) = IIF([Forms]![frmSearchCapability]![comboCapability] is
Null,0,1) +
IIF([Forms]![frmSearchCapability]![comboCapability2] is Null,0,1) +
IIF([Forms]![frmSearchCapability]![comboCapability3] is Null,0,1) +
IIF([Forms]![frmSearchCapability]![comboCapability4] is Null,0,1))

If the four combos will always have a selected value then you can simplify
that to
Criteria: IN (Select T.SupplierID
FROM TblSupplierCapabilityLink as T
WHERE T.CapabilityID = [Forms]![frmSearchCapability]![comboCapability] Or
T.CapabilityID = [Forms]![frmSearchCapability]![comboCapability2] Or
T.CapabilityID =[Forms]![frmSearchCapability]![comboCapability3] Or
T.CapabilityID = [Forms]![frmSearchCapability]![comboCapability4]
GROUP BY T.SupplierID
HAVING COUNT(*) = 4)


John,

Thanks, but I'm afraid I'm still not too hot on the old Access stuff -
where does this go?

Regards,

GKL


John said:
This would require a subquery in the where clause

Assumption:
tblSupplierCapabilityLink has a maximum of one record per combination of
Supplier and Capability

SELECT tblSuppliers.*
FROM tblSuppliers
WHERE TblSuppliers.SupplierID IN (
Select T.SupplierID
FROM TblSupplierCapabilityLink as T
WHERE T.CapabilityID = [Forms]![frmSearchCapability]![comboCapability] Or
T.CapabilityID = [Forms]![frmSearchCapability]![comboCapability2] Or
T.CapabilityID =[Forms]![frmSearchCapability]![comboCapability3] Or
T.CapabilityID = [Forms]![frmSearchCapability]![comboCapability4]
GROUP BY T.SupplierID
HAVING COUNT(*) = IIF([Forms]![frmSearchCapability]![comboCapability] is
Null,0,1) +
IIF([Forms]![frmSearchCapability]![comboCapability2] is Null,0,1) +
IIF([Forms]![frmSearchCapability]![comboCapability3] is Null,0,1) +
IIF([Forms]![frmSearchCapability]![comboCapability4] is Null,0,1))



[Sorry to re-post this, but was offered one solution before by
Vanderghast a while ago telling me to look at
http://www.mvps.org/access/queries/qry0016.htm, but that didn't seem to
do exactly what I wanted (or I couldn't implement it!). Wondered if
anyone had any new ideas.]


I've a database detailing various suppliers [tblSuppliers] and the
things they can do [tblCapabilities]. These are linked by
[tblSupplierCapabilityLink].

I currently have a working search form [frmSearchCapability] allowing
the user to select up to four capabilities from four combo boxes. The
form then updates a list box with all the companies that have ANY ONE
of these capabilities - an OR search.


I need also to provide an AND search - so only the companies that have
ALL the specified capabilities are returned. I'm happy with the results

of this appearing in a different list box, or even a different form,
but I can't get the search to work. The OR search currently has the
following criteria:


[Forms]![frmSearchCapability]![comboCapability] Or
[Forms]![frmSearchCapability]![comboCapability2] Or
[Forms]![frmSearchCapability]![comboCapability3] Or
[Forms]![frmSearchCapability]![comboCapability4].


How can I get the AND search to work in a similar way, ignoring Null
responses?


Any help -very- gratefully received!


Greg
Click to expand...
Click to expand...
Click to expand...
 
Thanks, but where do I put these criteria?
Click to expand...

In the bin.

After thinking about it I realised my suggestion almost certainly
wouldn't have worked, so I cancelled the message. Unfortunately,
Google Groups appears to have ignored the cancel request...
 
You are a marvellous person. Thank you infinitely.

GKL

John said:
That is a complete query and would be pasted into the SQL view of a query.
Open a new query, don't add any tables to it, use the menu View: SQL item to
switch to the text view. Paste the code into the sql view.
Try running the query.

IF that fails, then I've bollixed up the SQL somehow. You should get a
syntax error message of some type.

Another way to do this is in a COPY of your current query. Put the
following in the criteria under the Supplierid field

The criteria is all in one line, but to make it more readable, I've broken
it into multiple lines
Criteria: IN (Select T.SupplierID
FROM TblSupplierCapabilityLink as T
WHERE T.CapabilityID = [Forms]![frmSearchCapability]![comboCapability] Or
T.CapabilityID = [Forms]![frmSearchCapability]![comboCapability2] Or
T.CapabilityID =[Forms]![frmSearchCapability]![comboCapability3] Or
T.CapabilityID = [Forms]![frmSearchCapability]![comboCapability4]
GROUP BY T.SupplierID
HAVING COUNT(*) = IIF([Forms]![frmSearchCapability]![comboCapability] is
Null,0,1) +
IIF([Forms]![frmSearchCapability]![comboCapability2] is Null,0,1) +
IIF([Forms]![frmSearchCapability]![comboCapability3] is Null,0,1) +
IIF([Forms]![frmSearchCapability]![comboCapability4] is Null,0,1))

If the four combos will always have a selected value then you can simplify
that to
Criteria: IN (Select T.SupplierID
FROM TblSupplierCapabilityLink as T
WHERE T.CapabilityID = [Forms]![frmSearchCapability]![comboCapability] Or
T.CapabilityID = [Forms]![frmSearchCapability]![comboCapability2] Or
T.CapabilityID =[Forms]![frmSearchCapability]![comboCapability3] Or
T.CapabilityID = [Forms]![frmSearchCapability]![comboCapability4]
GROUP BY T.SupplierID
HAVING COUNT(*) = 4)


John,

Thanks, but I'm afraid I'm still not too hot on the old Access stuff -
where does this go?

Regards,

GKL


John said:
This would require a subquery in the where clause

Assumption:
tblSupplierCapabilityLink has a maximum of one record per combination of
Supplier and Capability

SELECT tblSuppliers.*
FROM tblSuppliers
WHERE TblSuppliers.SupplierID IN (
Select T.SupplierID
FROM TblSupplierCapabilityLink as T
WHERE T.CapabilityID = [Forms]![frmSearchCapability]![comboCapability] Or
T.CapabilityID = [Forms]![frmSearchCapability]![comboCapability2] Or
T.CapabilityID =[Forms]![frmSearchCapability]![comboCapability3] Or
T.CapabilityID = [Forms]![frmSearchCapability]![comboCapability4]
GROUP BY T.SupplierID
HAVING COUNT(*) = IIF([Forms]![frmSearchCapability]![comboCapability] is
Null,0,1) +
IIF([Forms]![frmSearchCapability]![comboCapability2] is Null,0,1) +
IIF([Forms]![frmSearchCapability]![comboCapability3] is Null,0,1) +
IIF([Forms]![frmSearchCapability]![comboCapability4] is Null,0,1))



[Sorry to re-post this, but was offered one solution before by
Vanderghast a while ago telling me to look at
http://www.mvps.org/access/queries/qry0016.htm, but that didn't seem to
do exactly what I wanted (or I couldn't implement it!). Wondered if
anyone had any new ideas.]


I've a database detailing various suppliers [tblSuppliers] and the
things they can do [tblCapabilities]. These are linked by
[tblSupplierCapabilityLink].

I currently have a working search form [frmSearchCapability] allowing
the user to select up to four capabilities from four combo boxes. The
form then updates a list box with all the companies that have ANY ONE
of these capabilities - an OR search.


I need also to provide an AND search - so only the companies that have
ALL the specified capabilities are returned. I'm happy with the results

of this appearing in a different list box, or even a different form,
but I can't get the search to work. The OR search currently has the
following criteria:


[Forms]![frmSearchCapability]![comboCapability] Or
[Forms]![frmSearchCapability]![comboCapability2] Or
[Forms]![frmSearchCapability]![comboCapability3] Or
[Forms]![frmSearchCapability]![comboCapability4].


How can I get the AND search to work in a similar way, ignoring Null
responses?


Any help -very- gratefully received!


Greg
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Using AND in Queries 1
Access Need help in making asset history in my current database. 1
help with query criteria with multiple chk boxes 5
If Statement with "*" in Query 2
Access Determine what query to run based on a checkbox 0
Query using text from form or anything if empty 2
Parameter Query 7
Combo Box in a Query 3

Back
Top