By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,934 Members | 1,441 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,934 IT Pros & Developers. It's quick & easy.

How to make access use empty string instead of NULL

P: 44
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?
Jul 29 '10 #1
Share this Question
Share on Google+
5 Replies


patjones
Expert 100+
P: 931
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
Jul 30 '10 #2

P: 44
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.
Jul 31 '10 #3

patjones
Expert 100+
P: 931
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
Jul 31 '10 #4

patjones
Expert 100+
P: 931
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
Jul 31 '10 #5

NeoPa
Expert Mod 15k+
P: 31,769
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.
Jul 31 '10 #6

Post your reply

Sign in to post your reply or Sign up for a free account.