Why is this SQL statement not working correctly? 1

[Paladyr]

Delete * From TableName Where Field1 Not In ('5555')

The problem is that in access, this statement WILL delete any records where Field1 = Null, but when running it through VB using a command object, it does not delete nulls. Any ideas why this isn't working? I know a workaround would be to do this:

Delete * from tablename where field1 Is Null or field1 <> '5555'

I'm just wondering if there is an explanation for why the first statement doesn't delete nulls. Thanks!

 

[Paladyr]

Okay my suggested workaround isn't even working. How do I get this thing to delete nulls?

 

[Paladyr]

Scratch that, it is working but the database isn't deleting quick enough for it to show on the report.

So back to the original question...

 

[malleykr]

The problem stems from the fact that null is not actually a value, but rather the absence of data. Because of this, you can not successfully compare it to a value. Access doesn't have a value, so it simply returns false to any inquiry containing null (other than a null=null relationship).

If you'd like, you can use the nz function.

Delete * From TableName Where nz(Field1) Not In ('5555')

nz can take some different forms, but in it's simplest (shown above) it converts null values into empty strings.

Good luck.

 

[Paladyr]

wow cool! I had not heard of &quot;nz&quot; .