Need multiple Qry criteria 1

[molly]

I have 2 fields (both are Number fields) in the query that i need some exclusion criteria. I do not want records which have the following criteria in either field.

On the 1st criteria row, the PacktypeID field the criteria of ..... <>999 and these records are correctly omitted.

and on the 2nd row, that says "or", the CatalogueID field is .... <>699 however, this criteria is ignored and i get those records and i didn't want them. so the second criteria row is being ignored.

any ideas? thanks

 

[PHV]

I'd use a single criteria row (ie AND instead of OR)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Golom]

Switch to SQL view and change the Where Clause to

WHERE PackTypeID <> 999 AND CatalogueID <> 699

It is not being ignored. You are generating a Where clause like

WHERE PackTypeID <> 999 [red]OR[/red] CatalogueID <> 699

If the PackType is <> 999 then the record is included, regardless of the value of CatalogueID.

 

[molly]

hi. yes, i already had the "And". here is my sql.
i get just 49 records when i should get 1,082 records.
Ideas?? thanks so much.


SELECT tblProduct.PriceTypeID, tblProduct.ProductID AS FormulaID, tblProduct.OldProductID, [8019Qry Specific Cost Detail].PriceLevelStd, tblProduct.Description, tblProduct.PackTypeID, tblProduct.CatalogueID, [8019Qry Specific Cost Detail].[USP Curr], tblProduct.SellUOM, tblProduct.StdConvFactor, [8019Qry Specific Cost Detail].[SSP Curr]
FROM [8019Qry Specific Cost Detail] LEFT JOIN (tblProduct LEFT JOIN tblPackType ON tblProduct.PackTypeID = tblPackType.PackTypeID) ON [8019Qry Specific Cost Detail].FormulaID = tblProduct.ProductID
WHERE (((tblProduct.PackTypeID)<>999) AND ((tblProduct.CatalogueID)<>699))
ORDER BY tblProduct.ProductID, [8019Qry Specific Cost Detail].PriceLevelStd, tblProduct.PackTypeID;

 

[molly]

and if i use the "OR" in the where clause, i get 1,091 records. the answer should be 1,082. the criteria is ignoring the <>699 code in catalogue field criteria.

 

[PHV]

Your criteria defeats the outer joins ...
You may try to replace this:
WHERE (((tblProduct.PackTypeID)<>999) AND ((tblProduct.CatalogueID)<>699))
with this:
WHERE Nz(tblProduct.PackTypeID,0)<>999 AND Nz(tblProduct.CatalogueID,0)<>699

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[molly]

Sun of a Gun. Your idea PHV worked great. How do you fellows think of these things !!!

I get my 1,082 records. For readers, here is the SQL that works nicely now: Molly

SELECT tblProduct.PriceTypeID, tblProduct.ProductID AS FormulaID, tblProduct.OldProductID, [8019Qry Specific Cost Detail].PriceLevelStd, tblProduct.Description, tblProduct.PackTypeID, tblProduct.CatalogueID, [8019Qry Specific Cost Detail].[USP Curr], tblProduct.SellUOM, tblProduct.StdConvFactor, [8019Qry Specific Cost Detail].[SSP Curr]
FROM [8019Qry Specific Cost Detail] LEFT JOIN (tblProduct LEFT JOIN tblPackType ON tblProduct.PackTypeID = tblPackType.PackTypeID) ON [8019Qry Specific Cost Detail].FormulaID = tblProduct.ProductID
WHERE (((Nz([tblProduct].[PackTypeID],0))<>999) AND ((Nz([tblProduct].[CatalogueID],0))<>699))
ORDER BY tblProduct.ProductID, [8019Qry Specific Cost Detail].PriceLevelStd, tblProduct.PackTypeID;

 

[Golom]

If you have

[tt]
PackTypeID CatalogueID
999 600
900 699
999 699
[/tt]

and you use

WHERE PackTypeID <> 999 [red]AND[/red] CatalogueID <> 699

Then none of these records would be included.

But if you use

WHERE PackTypeID <> 999 [red]OR[/red] CatalogueID <> 699

Then the first two records would be included even though each of them passes one of the two tests. Only the third record would be excluded.

 

[lespaul]

check out Understanding SQL Joins

Leslie

Have you met Hardy Heron?