Please help tweak my AND query.

R

Richard Horne

Hi guys, I have the following query:

SELECT Quotations.QuotationID, Quotations.Date,
Quotations.Quotation_Customer_Lookup, Quotations.[Your Reference],
Quotations.QuotationBy, Quotation_Details.[Item No],
Quotation_Details.Product_lookup, Quotation_Details.[Full Description],
Quotation_Details.[Type/Colour/Size], Quotation_Details.Quantity,
Quotation_Details.[Denomination Lookup], Quotation_Details.Price
FROM Quotations INNER JOIN Quotation_Details ON Quotations.QuotationID =
Quotation_Details.QuotationID
WHERE (((Quotations.Quotation_Customer_Lookup) Like "*" & [cboCompanyName])
AND ((Quotations.[Your Reference]) Like "*" & [QuoteRef]) AND
((Quotations.QuotationBy)=[cboQuoteBy]) AND
((Quotation_Details.Product_lookup) Like "*" & [cboProduct]) AND
((Quotation_Details.[Full Description]) Like "*" & [txtDescription] & "*")
AND ((Quotation_Details.[Type/Colour/Size]) Like "*" & [txtTypeColourSize] &
"*"))
ORDER BY Quotations.QuotationID DESC;


This filters out results according to selections made in combo boxes or by
text typed in txt boxes. But the results are only shown if two or more boxes
are filled in. What do I need to do to this query to make it so?

For example, one of the fields cboCompanyName contains a list of all our
customers. This query should show all quotations for the company chosen in
the list, but at the moment it will only show results if one of the other
querying combo/text boxes is filled in. I assume this is something to do with
the ANDs but I'm not sure.
 
O

Ofer

Hi Richard
How come this criteria
Quotations.QuotationBy)=[cboQuoteBy])

Is not like the rest of them, with like
Quotations.QuotationBy) Like "*" & [cboQuoteBy]

If the cboQuoteBy wont be selected then no value will be displayed.

--
I hope that helped
Good Luck


Richard Horne said:
Hi guys, I have the following query:

SELECT Quotations.QuotationID, Quotations.Date,
Quotations.Quotation_Customer_Lookup, Quotations.[Your Reference],
Quotations.QuotationBy, Quotation_Details.[Item No],
Quotation_Details.Product_lookup, Quotation_Details.[Full Description],
Quotation_Details.[Type/Colour/Size], Quotation_Details.Quantity,
Quotation_Details.[Denomination Lookup], Quotation_Details.Price
FROM Quotations INNER JOIN Quotation_Details ON Quotations.QuotationID =
Quotation_Details.QuotationID
WHERE (((Quotations.Quotation_Customer_Lookup) Like "*" & [cboCompanyName])
AND ((Quotations.[Your Reference]) Like "*" & [QuoteRef]) AND
((Quotations.QuotationBy)=[cboQuoteBy]) AND
((Quotation_Details.Product_lookup) Like "*" & [cboProduct]) AND
((Quotation_Details.[Full Description]) Like "*" & [txtDescription] & "*")
AND ((Quotation_Details.[Type/Colour/Size]) Like "*" & [txtTypeColourSize] &
"*"))
ORDER BY Quotations.QuotationID DESC;


This filters out results according to selections made in combo boxes or by
text typed in txt boxes. But the results are only shown if two or more boxes
are filled in. What do I need to do to this query to make it so?

For example, one of the fields cboCompanyName contains a list of all our
customers. This query should show all quotations for the company chosen in
the list, but at the moment it will only show results if one of the other
querying combo/text boxes is filled in. I assume this is something to do with
the ANDs but I'm not sure.
 
R

Richard Horne

Hi
Quotations.QuotationBy) Like "*" & [cboQuoteBy]

Is like that because when I had it as a Like "*" query, the results were
inconsistent. For example the two users with ids 6 and 16 would show the same
results because 6 is like 16.

I guessed that that line is the root of my problem but I can't figure out
how best to code it without using the Like statement.

Ofer said:
Hi Richard
How come this criteria
Quotations.QuotationBy)=[cboQuoteBy])

Is not like the rest of them, with like
Quotations.QuotationBy) Like "*" & [cboQuoteBy]

If the cboQuoteBy wont be selected then no value will be displayed.

--
I hope that helped
Good Luck


Richard Horne said:
Hi guys, I have the following query:

SELECT Quotations.QuotationID, Quotations.Date,
Quotations.Quotation_Customer_Lookup, Quotations.[Your Reference],
Quotations.QuotationBy, Quotation_Details.[Item No],
Quotation_Details.Product_lookup, Quotation_Details.[Full Description],
Quotation_Details.[Type/Colour/Size], Quotation_Details.Quantity,
Quotation_Details.[Denomination Lookup], Quotation_Details.Price
FROM Quotations INNER JOIN Quotation_Details ON Quotations.QuotationID =
Quotation_Details.QuotationID
WHERE (((Quotations.Quotation_Customer_Lookup) Like "*" & [cboCompanyName])
AND ((Quotations.[Your Reference]) Like "*" & [QuoteRef]) AND
((Quotations.QuotationBy)=[cboQuoteBy]) AND
((Quotation_Details.Product_lookup) Like "*" & [cboProduct]) AND
((Quotation_Details.[Full Description]) Like "*" & [txtDescription] & "*")
AND ((Quotation_Details.[Type/Colour/Size]) Like "*" & [txtTypeColourSize] &
"*"))
ORDER BY Quotations.QuotationID DESC;


This filters out results according to selections made in combo boxes or by
text typed in txt boxes. But the results are only shown if two or more boxes
are filled in. What do I need to do to this query to make it so?

For example, one of the fields cboCompanyName contains a list of all our
customers. This query should show all quotations for the company chosen in
the list, but at the moment it will only show results if one of the other
querying combo/text boxes is filled in. I assume this is something to do with
the ANDs but I'm not sure.
 
O

Ofer

Try this

Quotations.QuotationBy Like Nz([cboQuoteBy],"*")

--
I hope that helped
Good Luck


Richard Horne said:
Hi
Quotations.QuotationBy) Like "*" & [cboQuoteBy]

Is like that because when I had it as a Like "*" query, the results were
inconsistent. For example the two users with ids 6 and 16 would show the same
results because 6 is like 16.

I guessed that that line is the root of my problem but I can't figure out
how best to code it without using the Like statement.

Ofer said:
Hi Richard
How come this criteria
Quotations.QuotationBy)=[cboQuoteBy])

Is not like the rest of them, with like
Quotations.QuotationBy) Like "*" & [cboQuoteBy]

If the cboQuoteBy wont be selected then no value will be displayed.

--
I hope that helped
Good Luck


Richard Horne said:
Hi guys, I have the following query:

SELECT Quotations.QuotationID, Quotations.Date,
Quotations.Quotation_Customer_Lookup, Quotations.[Your Reference],
Quotations.QuotationBy, Quotation_Details.[Item No],
Quotation_Details.Product_lookup, Quotation_Details.[Full Description],
Quotation_Details.[Type/Colour/Size], Quotation_Details.Quantity,
Quotation_Details.[Denomination Lookup], Quotation_Details.Price
FROM Quotations INNER JOIN Quotation_Details ON Quotations.QuotationID =
Quotation_Details.QuotationID
WHERE (((Quotations.Quotation_Customer_Lookup) Like "*" & [cboCompanyName])
AND ((Quotations.[Your Reference]) Like "*" & [QuoteRef]) AND
((Quotations.QuotationBy)=[cboQuoteBy]) AND
((Quotation_Details.Product_lookup) Like "*" & [cboProduct]) AND
((Quotation_Details.[Full Description]) Like "*" & [txtDescription] & "*")
AND ((Quotation_Details.[Type/Colour/Size]) Like "*" & [txtTypeColourSize] &
"*"))
ORDER BY Quotations.QuotationID DESC;


This filters out results according to selections made in combo boxes or by
text typed in txt boxes. But the results are only shown if two or more boxes
are filled in. What do I need to do to this query to make it so?

For example, one of the fields cboCompanyName contains a list of all our
customers. This query should show all quotations for the company chosen in
the list, but at the moment it will only show results if one of the other
querying combo/text boxes is filled in. I assume this is something to do with
the ANDs but I'm not sure.
 
R

Richard Horne

That did the trick Ofer. Thank you very much mate.

Ofer said:
Try this

Quotations.QuotationBy Like Nz([cboQuoteBy],"*")

--
I hope that helped
Good Luck


Richard Horne said:
Hi
Quotations.QuotationBy) Like "*" & [cboQuoteBy]

Is like that because when I had it as a Like "*" query, the results were
inconsistent. For example the two users with ids 6 and 16 would show the same
results because 6 is like 16.

I guessed that that line is the root of my problem but I can't figure out
how best to code it without using the Like statement.

Ofer said:
Hi Richard
How come this criteria
Quotations.QuotationBy)=[cboQuoteBy])

Is not like the rest of them, with like
Quotations.QuotationBy) Like "*" & [cboQuoteBy]

If the cboQuoteBy wont be selected then no value will be displayed.

--
I hope that helped
Good Luck


:

Hi guys, I have the following query:

SELECT Quotations.QuotationID, Quotations.Date,
Quotations.Quotation_Customer_Lookup, Quotations.[Your Reference],
Quotations.QuotationBy, Quotation_Details.[Item No],
Quotation_Details.Product_lookup, Quotation_Details.[Full Description],
Quotation_Details.[Type/Colour/Size], Quotation_Details.Quantity,
Quotation_Details.[Denomination Lookup], Quotation_Details.Price
FROM Quotations INNER JOIN Quotation_Details ON Quotations.QuotationID =
Quotation_Details.QuotationID
WHERE (((Quotations.Quotation_Customer_Lookup) Like "*" & [cboCompanyName])
AND ((Quotations.[Your Reference]) Like "*" & [QuoteRef]) AND
((Quotations.QuotationBy)=[cboQuoteBy]) AND
((Quotation_Details.Product_lookup) Like "*" & [cboProduct]) AND
((Quotation_Details.[Full Description]) Like "*" & [txtDescription] & "*")
AND ((Quotation_Details.[Type/Colour/Size]) Like "*" & [txtTypeColourSize] &
"*"))
ORDER BY Quotations.QuotationID DESC;


This filters out results according to selections made in combo boxes or by
text typed in txt boxes. But the results are only shown if two or more boxes
are filled in. What do I need to do to this query to make it so?

For example, one of the fields cboCompanyName contains a list of all our
customers. This query should show all quotations for the company chosen in
the list, but at the moment it will only show results if one of the other
querying combo/text boxes is filled in. I assume this is something to do with
the ANDs but I'm not sure.
 

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

Top