How does a user insert a null in MS Access?

Question

MS Access appears to support nulls in code, but I can't for the life of me figure out how to enter a null directly in a table. This is maddening because once a field has had a figure entered in it, it can never be deleted/set to null. Normally, allowing zero length strings would take care of this, but Access treats the XML export of a null and a zero length string differently. A null eliminates the associated XML tag and a zero length string sends an empty tag.

Answer 1

Are you talking about needing to do it in Datasheet view? If you needed to do it in an update query, you can do it like this:

UPDATE test SET test.test = Null;

Answer 2

Just delete all characters in the column and access will insert a null value for you if the coumn allows it.

Answer 3

I'm actually looking for a way for a layperson to do the entry directly in a table. In MSSQL you can simply press ctrl-0 and a null is inserted. Deleting all the characters doesn't appear to work. Instead of a null, you are left with an empty string. They are not treated the same by Access for export purposes.

I think what I'm going to wind up doing is creating an xslt file to use during export that eliminates empty tags. That way the user can't really tell the difference.

It sure would be nice, though, to be able to key in a null.

Answer 4

Does setting the "Allow zero length string" option in the table definition help? It may force an empty string to be interpreted as NULL.

Answer 5

Ok, I don't think this is an easily solvable problem and my original question was a little off target. The problem isn't so much with nulls, although they are involved, as with the manner in which the table was originally created. If you use an xsd file to create your tables you plan on exporting, something happens under the hood in Access that causes those fields to be treated a little differently than fields created with the editors.

The only solution I found was to create a new field in the table, rename the old field, copy the data from the old field to the new field and delete the old field.

After doing that, then blank fields that had data in them at one point are no longer included in the XML output. It's probably not the best way to do this, and I still don't know exactly why it's happening, but at least I can get past the issue.

Answer 6

press ctrl-0 and a null is inserted.