Criteria returning wrong results/WHERE clause length

[Abiart]

Hi there,

I'm working on a select query that I have created through the visual query design window in Access 2003, I have multiple criteria but the query is not filtering the results properly.

I'm selecting only records :

• where the DeliveryCountry field is within the EU - works
• where the ProductID does not contain Repair, Upgrade or Rpr - doesn't work
• where the Demo/Sale ID is 2 - works

I've viewed the code in SQL view but I still can't locate the problem, though I suspect it lies in the treatment of ANDs and ORs.

The SQL is pasted below (I have reformatted with indentations so I hope it is easier to read).

One thing to note: the reason the countries list is split in two is because Access did this automatically when I had one long list. Is there a maximum length for WHERE clauses?

Thanks in advance for your help!

Abby

SELECT Orders.ShipDate, Products.[Standard Tarriff Number], [Order Details].[Quantity]*[Order Details].[unitprice]*(1-[discount])*(1-[special discount]) AS [Line Total], [Order Details].Quantity, Orders.OrdDeliveryCountry, Orders.OrderID, [Order Details].ProductID

FROM Products RIGHT JOIN (([Demo/Sale] RIGHT JOIN Orders ON [Demo/Sale].[Demo/SaleID] = Orders.[Demo/SaleID]) LEFT JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID) ON Products.ProductID = [Order Details].ProductID

WHERE 	(((Orders.OrdDeliveryCountry)="Austria" Or (Orders.OrdDeliveryCountry)="Belgium" Or (Orders.OrdDeliveryCountry)="Cyprus" Or (Orders.OrdDeliveryCountry)="Czech Republic" Or (Orders.OrdDeliveryCountry)="Denmark" Or (Orders.OrdDeliveryCountry)="Estonia" Or (Orders.OrdDeliveryCountry)="Finland" Or (Orders.OrdDeliveryCountry)="France" Or (Orders.OrdDeliveryCountry)="Germany" Or (Orders.OrdDeliveryCountry)="Greece" Or (Orders.OrdDeliveryCountry)="Hungary" Or (Orders.OrdDeliveryCountry)="Ireland" Or (Orders.OrdDeliveryCountry)="Italy" Or (Orders.OrdDeliveryCountry)="Latvia" Or (Orders.OrdDeliveryCountry)="Lithuania" Or (Orders.OrdDeliveryCountry)="Luxembourg" Or (Orders.OrdDeliveryCountry)="Malta")
		AND (([Order Details].ProductID) Not Like "*Upgrade") 
			AND ((Orders.[Demo/SaleID])=2)) 
	OR (((Orders.OrdDeliveryCountry)="Austria" Or (Orders.OrdDeliveryCountry)="Belgium" Or (Orders.OrdDeliveryCountry)="Cyprus" Or (Orders.OrdDeliveryCountry)="Czech Republic" Or (Orders.OrdDeliveryCountry)="Denmark" Or (Orders.OrdDeliveryCountry)="Estonia" Or (Orders.OrdDeliveryCountry)="Finland" Or (Orders.OrdDeliveryCountry)="France" Or (Orders.OrdDeliveryCountry)="Germany" Or (Orders.OrdDeliveryCountry)="Greece" Or (Orders.OrdDeliveryCountry)="Hungary" Or (Orders.OrdDeliveryCountry)="Ireland" Or (Orders.OrdDeliveryCountry)="Italy" Or (Orders.OrdDeliveryCountry)="Latvia" Or (Orders.OrdDeliveryCountry)="Lithuania" Or (Orders.OrdDeliveryCountry)="Luxembourg" Or (Orders.OrdDeliveryCountry)="Malta") 
		AND (([Order Details].ProductID) Not Like "*Repair") 
			AND ((Orders.[Demo/SaleID])=2)) 
	OR (((Orders.OrdDeliveryCountry)="Austria" Or (Orders.OrdDeliveryCountry)="Belgium" Or (Orders.OrdDeliveryCountry)="Cyprus" Or (Orders.OrdDeliveryCountry)="Czech Republic" Or (Orders.OrdDeliveryCountry)="Denmark" Or (Orders.OrdDeliveryCountry)="Estonia" Or (Orders.OrdDeliveryCountry)="Finland" Or (Orders.OrdDeliveryCountry)="France" Or (Orders.OrdDeliveryCountry)="Germany" Or (Orders.OrdDeliveryCountry)="Greece" Or (Orders.OrdDeliveryCountry)="Hungary" Or (Orders.OrdDeliveryCountry)="Ireland" Or (Orders.OrdDeliveryCountry)="Italy" Or (Orders.OrdDeliveryCountry)="Latvia" Or (Orders.OrdDeliveryCountry)="Lithuania" Or (Orders.OrdDeliveryCountry)="Luxembourg" Or (Orders.OrdDeliveryCountry)="Malta") 
		AND (([Order Details].ProductID) Not Like "*Rpr") 
			AND ((Orders.[Demo/SaleID])=2)) 

	OR (((Orders.OrdDeliveryCountry)="Holland" Or (Orders.OrdDeliveryCountry)="Poland" Or (Orders.OrdDeliveryCountry)="Portugal" Or (Orders.OrdDeliveryCountry)="Slovakia" Or (Orders.OrdDeliveryCountry)="Slovenia" Or (Orders.OrdDeliveryCountry)="Spain" Or (Orders.OrdDeliveryCountry)="Sweden") 
		AND (([Order Details].ProductID) Not Like "*Upgrade") 
			AND ((Orders.[Demo/SaleID])=2)) 
	OR (((Orders.OrdDeliveryCountry)="Holland" Or (Orders.OrdDeliveryCountry)="Poland" Or (Orders.OrdDeliveryCountry)="Portugal" Or (Orders.OrdDeliveryCountry)="Slovakia" Or (Orders.OrdDeliveryCountry)="Slovenia" Or (Orders.OrdDeliveryCountry)="Spain" Or (Orders.OrdDeliveryCountry)="Sweden") 
		AND (([Order Details].ProductID) Not Like "*Repair") 
			AND ((Orders.[Demo/SaleID])=2)) 
	OR (((Orders.OrdDeliveryCountry)="Holland" Or (Orders.OrdDeliveryCountry)="Poland" Or (Orders.OrdDeliveryCountry)="Portugal" Or (Orders.OrdDeliveryCountry)="Slovakia" Or (Orders.OrdDeliveryCountry)="Slovenia" Or (Orders.OrdDeliveryCountry)="Spain" Or (Orders.OrdDeliveryCountry)="Sweden") 
		AND (([Order Details].ProductID) Not Like "*Rpr") 
			AND ((Orders.[Demo/SaleID])=2))

ORDER BY Orders.ShipDate DESC;

 

[PeterF]

You are right that it lies in the logic, You are testing different OR's, I think it should look like below. I don't know why access split's the country string but left it split so you can see what was wrong with your SQL.

((((Orders.OrdDeliveryCountry)="Austria" Or (Orders.OrdDeliveryCountry)="Belgium" Or (Orders.OrdDeliveryCountry)="Cyprus" Or (Orders.OrdDeliveryCountry)="Czech Republic" Or (Orders.OrdDeliveryCountry)="Denmark" Or (Orders.OrdDeliveryCountry)="Estonia" Or (Orders.OrdDeliveryCountry)="Finland" Or (Orders.OrdDeliveryCountry)="France" Or (Orders.OrdDeliveryCountry)="Germany" Or (Orders.OrdDeliveryCountry)="Greece" Or (Orders.OrdDeliveryCountry)="Hungary" Or (Orders.OrdDeliveryCountry)="Ireland" Or (Orders.OrdDeliveryCountry)="Italy" Or (Orders.OrdDeliveryCountry)="Latvia" Or (Orders.OrdDeliveryCountry)="Lithuania" Or (Orders.OrdDeliveryCountry)="Luxembourg" Or (Orders.OrdDeliveryCountry)="Malta")))
	OR (((Orders.OrdDeliveryCountry)="Holland" Or (Orders.OrdDeliveryCountry)="Poland" Or (Orders.OrdDeliveryCountry)="Portugal" Or (Orders.OrdDeliveryCountry)="Slovakia" Or (Orders.OrdDeliveryCountry)="Slovenia" Or (Orders.OrdDeliveryCountry)="Spain" Or (Orders.OrdDeliveryCountry)="Sweden")))
)
	
	AND (([Order Details].ProductID) Not Like "*Upgrade" and ([Order Details].ProductID) Not Like "*Repair") AND (([Order Details].ProductID) Not Like "*Rpr")
	AND ((Orders.[Demo/SaleID])=2))

 

[Abiart]

Hi Peter,

Thanks very much for your response.

Unfortunately when I try your code access tells me there is an extra ) between '((((Orders.OrdDeliveryCountry="Austria"' and 'Orders.OrdDeliveryCountry="Estonia"'.

Any ideas why that might be?

Is it possible to build the structure you suggest in the query design window?

Thanks for your help!

Abby

 

[Abiart]

I tried to build the structure set out by Peter's SQL using Design View. The SQL that came of that is below (no parantheses problems).

Unfortunately it's still returning ProductID's "*Repair", "*Upgrade" and "*Rpr".

Here's the bit I'd like to highlight as the problem area:

WHERE (Orders.OrdDeliveryCountry)="List of EU Countries" AND (([Order Details].ProductID) Not Like "*Upgrade" And ([Order Details].ProductID) Not Like "*Repair" And ([Order Details].ProductID) Not Like "*Rpr") AND ((Orders.[Demo/SaleID])=2))

Here's the whole statement:

SELECT Orders.ShipDate, Products.[Standard Tarriff Number], [Order Details].[Quantity]*[Order Details].[unitprice]*(1-[discount])*(1-[special discount]) AS LineTotal, [Order Details].Quantity, Orders.OrdDeliveryCountry, Orders.OrderID, [Order Details].ProductID

FROM Products RIGHT JOIN (Orders LEFT JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID) ON Products.ProductID = [Order Details].ProductID

WHERE (((Orders.OrdDeliveryCountry)="Austria" Or (Orders.OrdDeliveryCountry)="Belgium" Or (Orders.OrdDeliveryCountry)="Cyprus" Or (Orders.OrdDeliveryCountry)="Czech Republic" Or (Orders.OrdDeliveryCountry)="Denmark" Or (Orders.OrdDeliveryCountry)="Estonia" Or (Orders.OrdDeliveryCountry)="Finland" Or (Orders.OrdDeliveryCountry)="France" Or (Orders.OrdDeliveryCountry)="Germany" Or (Orders.OrdDeliveryCountry)="Greece" Or (Orders.OrdDeliveryCountry)="Hungary" Or (Orders.OrdDeliveryCountry)="Ireland" Or (Orders.OrdDeliveryCountry)="Italy" Or (Orders.OrdDeliveryCountry)="Latvia" Or (Orders.OrdDeliveryCountry)="Lithuania" Or (Orders.OrdDeliveryCountry)="Luxembourg" Or (Orders.OrdDeliveryCountry)="Malta" Or (Orders.OrdDeliveryCountry)="Holland" Or (Orders.OrdDeliveryCountry)="Poland" Or (Orders.OrdDeliveryCountry)="Portugal" Or (Orders.OrdDeliveryCountry)="Slovakia" Or (Orders.OrdDeliveryCountry)="Slovenia" Or (Orders.OrdDeliveryCountry)="Spain" Or (Orders.OrdDeliveryCountry)="Sweden") AND (([Order Details].ProductID) Not Like "*Upgrade" And ([Order Details].ProductID) Not Like "*Repair" And ([Order Details].ProductID) Not Like "*Rpr") AND ((Orders.[Demo/SaleID])=2))

ORDER BY Orders.ShipDate DESC;

 

[PeterF]

I can't see anything wrong with your SQL, what kind of field is "ProductId"?
If this is a lookup field based on a ID-number you have to reference the field that contains the full description.

For a easy way to format your SQL in a more readable form:
http://www.wangz.net/cgi-bin/pp/gsqlparser/sqlpp/sqlformat.tpl

 

[Abiart]

Result!

ProductID is an ID but has a combo lookup; the combo is set in the Order Details table, and looks up 2 columns from a Products table - the ProductID and the Product Name.

If this is a lookup field based on a ID-number you have to reference the field that contains the full description.

Ah it has just dawned on me! Of course it's still showing all ProductID's because non of the ID's contain text! I knew it would be some silly mistake.

Thanks very much for your help!