MS Access default value

Question

I have a text column in one of my MS Access tables that is empty be default when a new record is inserted. The problem I have is that I can't seem to check whether this field is empty with EITHER equals Null or equals "" . For example, neither of these "reads" the field as being empty:

If [Field] = "" Or [Field] = Null Then

I've always used Access 2007 to develop this database, and I recently opened it with Access 2003, which is when I think this problem started. Could that be the cause? If so, would simply opening and saving it again with Access 2007 solve it?

Answer 1

When you compare against null, you need to use the IsNull function. With traditional ANSI SQL logic, Null <> Null, so you need a special function to test for null.

If [Field] = "" Or IsNull([Field])

Answer 2

First off, I would suggest that you do one of two things:

1. set your fields to disallow zero-length strings. That way you'd have to test only Is Null.

2. if you feel you must allow storage of ZLS, then set the default value to a ZLS. It's still possible for the field to end up Null, though, so this may or may not be helpful.

I don't see storage of ZLS's as having any utility whatsoever. It's almost always a shortcut to accomodate data being appended from sources that return ZLS's instead of Nulls for empty fields.

You should also read up on Nulls. Allen Browne has a number of pages that explain it all quite well:

• Nulls: Do I need them?
• Common Errors with Null

Aspects of working with Nulls in VBA code:

• Nothing? Empty? Missing? Null?

The articles are Access-oriented, but could be valuable to those using any database, particularly relative novices because of the conversational style of the writing.

Answer 3

You should try:

If Nz([Field], "") = "" Then 

Because:

If [Field] = ""

in:

If [Field] = "" Or IsNull([Field])

Would still throw a Null Error!