Is Not Null is not working on a text field?

  • Thread starter Thread starter Nathon Jones
  • Start date Start date
N

Nathon Jones

Hi,

I have a simple events table, and one field is "category". Some events
don't have a category, so the 50 character text field is left blank.

I run a query on the table, that is basically WHERE category IS NOT NULL,
but when I open my query, it is still displaying events that have no entry
(IS NULL!), in the event category field!

What am I missing here?

Here's the SQL view of it:

SELECT *
FROM tblEvents
WHERE (((tblEvents.eventcategory) Is Not Null))
ORDER BY tblEvents.eventcategory;

Seems a really straightforward query to me, but it's not happening.
Any advice would be greatly appreciated. Thanks.

Nath.
 
Perhaps the field contains a zero-length string (ZLS) instead of a Null.

Open your table in design view.
Select the EventCategory field.
In the lower pane, check that the Allow Zero Length property is set to No.

In the first 6 versions of Access, this property defaulted to No. For some
incomprehensible reason, it changed to Yes by default in the last two
versions, so you must set it to No manually every time you add a text field
to any table. (It must be No, because it it too confusing for users. Even
Access can't tell the difference properly: DLookup() gets it wrong and
returns Null when the value is a ZLS.) You may need to run an Update query
to change "" to Null.

It that is not the issue, is EventCategory indexed? If so, the index may be
corrupt, and you should be able to revive it with a repair: Tools | Database
Utilities | Compact/Repair.
 
Hi Allen,

I've opened the table and, indeed, Allow Zero Length was set to Yes. I've
changed this to No, but this hasn't solved my issue.

What is the update query to change "" to Null? That's not something I am
very familiar with to be honest.

There is no index, but I've run the Compact/Repair tool anyway...still no
change.

Is this a software bug? I'm using Access 2003.

Thanks for you help.
Nath.
 
1. Create a query into this table.

2. Change it to an Update query: Update on query menu.
Access adds an Update row to the grid.

3. Drag the EventCategory field into the grid.
In the Critiera row enter:
""
In the Update row, enter:
Null

4. Run the query.

That changes all existing zero-length strings in into Null.
 
Terrific Allen,
Worked a treat. Thanks.

Nath.

Allen Browne said:
1. Create a query into this table.

2. Change it to an Update query: Update on query menu.
Access adds an Update row to the grid.

3. Drag the EventCategory field into the grid.
In the Critiera row enter:
""
In the Update row, enter:
Null

4. Run the query.

That changes all existing zero-length strings in into Null.
Click to expand...
 
Back
Top