Query using 2 'not' 2

[lawdy]

One of these days I may learn how to post property. I have redone my original post to include the entire code. It was something like:

DELETE aTempQuery.fDblItemOrder, aTempQuery.fTxtItemMaterial
FROM aTempQuery
WHERE ((Not (aTempQuery.fTxtItemMaterial)="ATredPaint")) OR ((Not (aTempQuery.fTxtItemMaterial)="ATgraPaint"));

That deletes every record in the table. Someone subjected changing 'or' to 'and' which I did in the following code:

' changing 'or' to 'and'
strQuery = "DELETE aTempQuery.fDblItemOrder, aTempQuery.fTxtItemMaterial " _
& "From aTempQuery " _
& "WHERE ((Not (aTempQuery.fTxtItemMaterial)='ATredPaint')) and ((Not (aTempQuery.fTxtItemMaterial)='ATgraPaint'));"


' deleted every record in table - I don't want 'ATredPaint' or 'ATgraPaint' deleted

It also deleted all records in the table. Someone else said I needed to eliminate the 'not'. There are 2 'not's in the code, so I eliminated the 2nd one:

' changing 'or' to 'and' and eliminated one of the 'not's
strQuery = "DELETE aTempQuery.fDblItemOrder, aTempQuery.fTxtItemMaterial " _
& "From aTempQuery " _
& "WHERE ((Not (aTempQuery.fTxtItemMaterial)='ATredPaint')) and (aTempQuery.fTxtItemMaterial='ATgraPaint');"
' Query did nothing to table

This did nothing to the records. I hope this better explains what I am trying to do. Thanks in advance.

[pbaldy]

The suggestion to change OR to AND was correct, as explained there. The suggestion to not use "Not" was also to replace = with <>. These are the same:

Not A = B
A <> B

But most of us use the latter syntax. Since you're building the SQL in code, use this method to debug it:

https://www.baldyweb.com/ImmediateWindow.htm

which will let you test the SQL in a new query.

[lawdy]

I tried the suggests as shown in the next few lines:

strQuery = "DELETE tblTemporary.fDblItemOrder, tblTemporary.fTxtItemMaterial " _
& "From tblTemporary " _
& "WHERE (((tblTemporary.fTxtItemMaterial)<>'ATredPaint' And (tblTemporary.fTxtItemMaterial)<>'ATgraPaint'));"

It deleted all records in the table. What else do I do?

[lawdy]

Here is the table in case that may help.

fDblItemOrder	fTxtItemMaterial
2000	FIRELANE
2001	firelane RBredPaint
2002	firelane GBredPaint
2007	firelane RBgraPaint
2021	firelane PAredPaint
2070	firelane washPP
2080	firelane sandblast
1001.0101	firelane ATredPaint
1001.0102	firelane ATwhiPaint
1001.0103	firelane ATyelPaint
1001.0104	firelane ATbluPaint
1001.0105	firelane ATblaPaint
1001.0106	firelane ATtecPaint
1001.0107	firelane ATgrePaint
1001.0108	firelane ATgraPaint

[pbaldy]

It appears that the field is not equal to either of your values, the values all include "firelane ". Add that or use Like and wildcards.

[lawdy]

That was it. It works perfect now. Thank you very much. If I can mark this solved now, everything will be ok.

[pbaldy]

Happy to help!