Delete query not working - why? 1

[kjv1611]

I'm no expert in Queries and SQL yet, so bear with me. Here is the code for the query I am running:

DELETE tblStatusAudit.*, tblStatusAudit.DNUM
FROM tblStatusAudit
WHERE (((tblStatusAudit.DNUM) Is Null));

The query runs, gives warning messages, whether I run it as a query or run it in VBA code. So it's running, but it's not finding any records. I know that I have at least one record currently in the table that meets the criteria - as far as I can tell. The field, "DNUM", is showing up blank in the table, but there is a record for it. Is there something else I could use to make the comparison other than Is Null?

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[PHV]

Have you tried this ?
DELETE * FROM tblStatusAudit
WHERE DNUM Is Null;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[kjv1611]

Tried changing to that, and still no go. Could it be some different value that I need to check for besides Null??

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[PHV]

And this ?
WHERE Trim(DNUM & "")="";

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[kjv1611]

Great! That worked like a charm! Could you give me a clue, or some point of reference to help understand why the Trim(DNUM & "") = " would work when the Is Null did not?

Have a star!

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[kjv1611]

Also, this is just a guess, but would it be best to check for Is Null as well as the trim deal as a general rule, in case there may be some Nulls and some that have 0 space strings in them?

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[PHV]

The where clause should work even if DNUM is null as the & concatenation operator admits null unlike the + operator.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[kjv1611]

Thanks for the help!

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV