Check for Null Or 0 in Delete Query

[Jay]

I want a delete query which deletes all records where a specific field
is either Null or 0 (zero).

However, entering 'Is Null Or 0' in the criteria field of the delete
query doesn't seem to work? It deletes the Null records, but the 0
records remain.

Can anyone advise what I'm doing wrong.

Many TIA

Jason

 

[Nick Coe \(UK\)]

If you create a select query with those same criteria does
it work correctly?

The most common cause for not returning rows where Is Null
is used iin the criteria is that they don't actually contain
Null but rather are either zero length strings or a space.

If you can get a straight forward select query to work and
get you the rows you expect then a delete query with the
same criteria should work. Use the select query to do your
troubleshooting.

--
Nick Coe (UK)
http://www.alphacos.co.uk/




In Jay typed:

 

[RoyVidar]

I want a delete query which deletes all records where a specific
field is either Null or 0 (zero).

However, entering 'Is Null Or 0' in the criteria field of the delete
query doesn't seem to work? It deletes the Null records, but the 0
records remain.

Can anyone advise what I'm doing wrong.

Many TIA

Jason

Could it be that the field in question is either single or double, and
the result of a calculation that is close to 0 but not entirely 0?

In such case, one could try something along the lines of

....WHERE MyField Is Null or ABS(MyField)<0.00005

this is how the SQL view of the query might look for a field called
MyField. If this is the case, you will have to evaluate what is a
"safe"
calculation of "what is close enough to 0 to qualify for deletion".

 

[Sylvain Lafontaine]

Did you have written « Is Null or 0 » verbatim or you have written something
like « Field1 is Null or Field1 = 0 » ?

It would have been a good idea if you had posted your exact statement and
the type of the field.

 

[Jay]

Thanks for the replies everyone. I'm not at work at the moment. I'll
try again tomorrow & post my exact criteria.

Thanks

Jason