AND criteria

[JCross]

Hi!

I have a query, and I want to eliminate a record that has an ID of "I077" AND a lastcost of 0. I thought that if you put the criteria on the same line, it would produce and AND result, but it gets rid of ALL records that have an ID of "I077" and then eliminates all records that have a lastcost of 0. HELP!

Jennifer

 

[RV]

Go the the SQL View of your query.
Look whether your conditions include the OR operator.
If so, change it to AND.
BTW, before running a DELETE statement, it's advised to create a similar
SELECT statement first to check whether the correct data is retrieved...

Put your criteria on a separate line using the Design View.

Hope you didn't loose your data.

RV

 

[JCross]

I checked the SQL and it does say AND - but it still first eliminates the "I077" and then eliminates the items that cost 0.00...............but I only want it to eliminate an item that is I077 AND costs 0.00..........

confused.

 

[simongallop]

See if this (when amended to fit your table / field names) works.

SELECT * FROM [TableName]
WHERE ([IDFieldName] = "I077" AND [CostFieldName]=0.00);

HTH

 

[JCross]

WHERE (Not ((tblIngredient.ItemNumber)="I077") AND (Not(qryFinalAvgCost.LastCost)="0"));

This is what I have in the query, and if I take out the "not"s I can isolate the item I want to get out of the query (i'm not deleting it) but with the "not"s in the query doesn't apply the AND! It gets rid of all the I077's and then all the items that cost 0. ARGH!

Jennifer

any more ideas out there/

 

[RV]

>WHERE (Not ((tblIngredient.ItemNumber)="I077") AND (Not(qryFinalAvgCost.LastCost)="0"));<

Seems we're talking about different rows in diffferenyt tables here.
Your first condition excludes all row in your table tblIngredient having an ItemNumber which equals 1077.
The second condition excludes all row retrieved by your query qryFinalAvgCost having a LastCost equalling 0.

In other words, your query results are correct.
In other words, your WHEREclause isn't correct.
You'll need a join in your query to join tblIngredient and qryFinalAvgCost.

RV

 

[JCross]

Thank you! Of course, that makes sense now........

A great weekend to all!

Jennifer

 

[Pat Hartman]

NOTs can be confusing. Expecially in compund conditions. You want to eliminate a record where two conditions are true. Therefore, you need a compound condition to identify that condition. You then want to "NOT" that compound condition to eliminate the records that match the condition rather than select them.

WHERE Not (tblIngredient.ItemNumber ="I077" AND qryFinalAvgCost.LastCost ="0");

 

[JCross]

Thank you for explaining it clearly. I only had it working because I'd tried putting the NOT everywhere, but I wasn't understanding exactly why the one that worked, did. Now I do Thanks.