Allow null in date field

[Joe]

How do you configure a date field to allow null values? I
have a table with existing records which the user is
allowed to update. At times, a user will want to remove a
date from a field. However, when my VB app attempts to
update the record, it tells me the field does not allow
null values. What am I missing?

 

[Tim Ferguson]

However, when my VB app attempts to
update the record, it tells me the field does not allow
null values

First of all, check that the field in the table is not marked as Required.

Second of all, if you really mean VB, is to realise that textboxes in VB
return "" (i.e. a zero-length string) when they are empty, in contrast to
Access textboxes which return NULL. The importance of this is that NULL is
a valid thing to send to a DateTime field, but "" is not. In VB, if you are
using a Data control, you have to trap the Validate event and change any ""
textbox values into NULL. More details in one of the VB groups.

You don't need to do this in Access

Hope that helps


Tim F

 

[IvoryT]

Joe,
Search on "required property" in Access help. xplains it
pretty well.
- tw

 

[Joe]

Thanks for the ideas. I do have the required property set
to "No" and in my VB code, when I encounter "" from the
text box, I set the variable used to update to "NULL".
However, when I issue the update command, I receive an
error that NULL values are not allowed.

Is there something else I should be using besides the
keyword "NULL"?

Thanks again.

 

[IvoryT]

I ran across mention of a function called Null() in some
related material. Perhaps that is what VB wants.
- tw

 

[Tim Ferguson]

Thanks for the ideas. I do have the required property set
to "No" and in my VB code, when I encounter "" from the
text box, I set the variable used to update to "NULL".
However, when I issue the update command, I receive an
error that NULL values are not allowed.

What code _exactly_ are you using to update the record? These should all
work fine:

strSQL = "UPDATE MyTable SET MyDate=NULL WHERE IDNum = 2"
db.Execute strSQL, dbFailOnError

and this one, using a recordset

Set rs = db.OpenRecordset(strSomething, dbOpenDynaset)
rs.MoveFirst
rs.Edit
rs!MyDate = Null
rs.Update
rs.Close

There are ADO equivalents if you prefer, although I cannot be bothered to
learn it. If you are getting stuck in VB, have you tried one of the vb
groups? As you have found, Access VBA is slightly different.

B Wishes


Tim F