-
Query using 2 'not' 2
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.
-
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.
-
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?
-
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 |
-
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.
-
That was it. It works perfect now. Thank you very much. If I can mark this solved now, everything will be ok.
-
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules