Invalid Use of NULL

[debbie1212]

If someone does not make a selection in the dropdown menu box, it is considered blank. Therefore it should pass "NULL" to the database.

However when I do the following code it returns an error message that says "Invalid use of Null".

If Len(Trim$(txtSentence.Text)) = 0 And icboTime.SelectedItem.Index = 1 Then
sSentence = Null

Anyone know why?

Thanks,
Debbie

 

[mattKnight]

Debbie1212

How is sSentence defined - from hungarian notation and the error I would guess string

You can't set a string to NULL, only variants
try using vbNullString

Hope that helps


Matt

 

[debbie1212]

Yes it is a string.

I tried what you suggested and I no longer get the error message but it still doesn't put a NULL in the database field. Is there a way to do it with a string? Unfortunately, the way the code is written it doesn't work with a space in the field. I could probably get it to work that way although I've been trying. Besides, then this field was added to the table, all of the existing records were given an NULL for this field so I want to be consistent.

 

[mattKnight]

Debbie1212

How are you updating the database?

can you add a test of sSentence to the update to set the field to NULL?

Matt

 

[debbie1212]

I'm sorry but I'm not sure how to add a test of sSentence.

 

[mattKnight]

Debbie1212,

It was late when I posted last,

something like

if sSentence = vbNullString then
  'set database field to NULL
  'and update the rest of the fields
else
  'Valid data in sSentence - need to update database
end if

is what I meant, many apologies if it was poorly phrased.

I fyou have more problems post the DB update code, and we'll go from there

Matt

 

[debbie1212]

There is a problem with this website and I keep trying to send this reply and keep getting an error message. Hopefully I don't wind up with this getting entered 5 times but here is my reply again:


Thanks for the information on how to test it. Learning how to troubleshoot is half the battle.

Your suggestion on using “sSentence = vbNullString” got rid of the error I was having since it sends a blank to the database instead of a space. However in an attempt to get a NULL in there to be consistent with the rest of the database, I changed the code in the Update stored procedure so that when the case is updated, and the field is empty, enter a NULL. Of course this will only work if it is updated and not initially entered but that’s better than nothing.

I will try your suggestion for adding a NULL to the database. I am working on another rush task right now so I’ll have to try it a little bit later. So it sounds like I can add a NULL to the database even though it’s a string. That would be nice. I will try the following soon:

if sSentence = vbNullString then
sSentence = NULL
else
'Valid data in sSentence - need to update database
end if

Thank you so much for all of your help.

Debbie