Please help tweak my AND query.

[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.

 

[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

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.

 

[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.

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.

 

[Ofer]

Try this

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

--
I hope that helped
Good Luck

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.

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.

 

[Richard Horne]

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

Try this

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

--
I hope that helped
Good Luck

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.

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.