Can not update NULL value.

[Steve Thomas]

I have a TextBox linked to a field in a database which is a date field. When the record is first accessed the value is null. When I change the value to a valid date it is fine. However, when I try and change it back to a blank value

me.text1.text = ""

it gives me a data integrity error.

Any Ideas???

[seaweed]

I'm guessing that the problem is that an empty string is not the same thing as NULL. You might have to send back the value NULL instead of an empty string because of some data validation going on at the database end.

Before sending the data back, you might try this...

If Text1 = "" Then
    Text1 = vbNullString
End If

I'm not sure this is going to fix it, but I think it's on the right track.

Edited by seaweed on 03-08-2000 at 07:17 PM

[Steve Thomas]

I tried this but it still does not like it. Apparently once a valid date value is entered, it will only accept a valid date and not null.

Any other ideas on how to deal with this?

You can check a null value with.

If IsNull(Text1.Text) Then

And when you want to give a field a null value.
make sure you have defined the field in de database
to accept the null values. Mostly the default value
is (do not accept null value).

-Kayoca

[Steve Thomas]

The database is Access and I can not find anywhere where you designate that it is okay to accept null values. I am converting this App from Access to VB and it works fine in access forms. You can somehow place Null values in an access form but if you are using a data control in VB it treats it differently????????????

[compuGEEK]

In Access itself, you can set the Allow Nulls property to true.

Also, using this will change null to an empty string " "
form.txtField=format(rs("Field"))

[Steve Thomas]

You can set a text field to allow zero length but I can not find where you can do it with a field formated as a date? I am using Access97. Are you using Access 2000? If you are using Access97 then could you be more specific and tell me how I do it.

Thanks