How to make access use empty string instead of NULL

[labmonkey111]

I have a table in SQLServer with a varchar field set to NOT NULL, with a default value of the empty string. This table is linked in access, and the field is linked to a text box on a form. When I attempt to edit that field, all is good unless I need to delete everything in the text box and return it to the default state of the empty string, Access complains: "You tried to assign the NULL value to a variable that is not a variant data type". I try to edit the field from the table directly (in access) and I get the same error when attempting to set it to the empty string. I would like to know if there is any way to get access to use the empty string instead of NULL in this situation? I'd rather not resort to MS's suggestion of allowing NULLs and then just pretending that "" and NULL are the same. Any ideas?

[patjones]

I just duplicated your situation using SQL Server Management Studio and got exactly the same message. What is even more interesting is that when I modify the column on the SQL Server side to allow nulls, the error persists.

Looking in other forums online, I am finding lots of people who received this message - but it was usually in the context of using a form for the data entry. The workaround appears to be to check in the text box's After Update event to make sure the form isn't passing a null to the table.

However, in my test, I was attempting to modify the record directly in the Access table. Fundamentally, it seems to me that if the column is set to allow nulls on the SQL Server side, and then I go and and completely delete an entry in a column on the Access side...it should let me do it.

I will try and research it some more. In the meantime, others here on the forum may have an idea.

Pat

[labmonkey111]

I eventually started thinking that using the After Update event might work, at least for the form side of things. Which for my purpose would be adequate, since I'm only concerned about the user's experience. I can manually run an UPDATE statement if I need to. But since this was a project for my job, I didn't want to spend any more time on it than was necessary, so I just went the way of allowing nulls. Maybe I'll try the After Update solution next time.

[patjones]

I was about to say that you should just allow nulls when I responded to you the other night, but that was before I actually did allow nulls and still got the message.

The error did in fact disappear for you after allowing nulls on the SQL Server side?

Pat

[patjones]

Actually it does work just fine for me when I allow nulls in the SQL Server table design. A little bit of confusion about column names on my part ;)

Pat

[NeoPa]

I think a lot of the confusion here arises from the controls used. Controls are not really typable per-se. They can be bound to typed fields, but in all cases they will interpret an empty control as a Null (No quotes used even for strings when entering data). There's no reliable way to differentiate between an empty string ("") and Null from the perspective of a form control, and even numeric entries are treated as strings that just get converted when assigned to the underlying field.