How do I set date fields to accept nulls

[eshimano]

thank you JHausmann for your answer,

How do I set my date fields to accept null values as well as dates?

S.T.

[JHausmann]

Open database using Access
Open the table in design mode
select the field that you want to modify
make sure that allow zero length is set to yes

[eshimano]

Thank's again for your help,

What you are saying is the very thing I am trying to do.

When you have a date/time field in an access database there is no option to allow zero length strings so how do you get it to accept them? or nulls?

[JHausmann]

Sorry about that, I missed the fact that the allowzerolength property only applies to text, memo and hyperlink table fields.

Setting the required property to no should allow nulls.

[eshimano]

Hi again:

It dosn't work. I need a formula in the validation rule? something like: allow nulls and dates.

thank's, esti.

[JHausmann]

Unfortunately, I don't know much of anything about Access's validation rules. You could try the following which I know would work.

1) hide (form.field.visible=false) the date field
2) add a new text field that is not linked to the database to allow the user to enter/modify dates
3) on form load (or tab load or whatever method updates the screen) set the text field = to the hidden database field.
4) on the text field lost focus set the hidden date field = to what the user entered if it's a vaild date or 0 if its not (or some date they're not likely to use).

[bashfirst]

If the required property is set to No for the date field, why can't you insert nulls for the date field....

The following insert statement runs alright in Access97 (with the appropriate tbl defined)

CurrentDB.Execute "INSERT INTO tbl (TextField, NumberField, DateField) VALUES ('A string', 123, NULL)"

Bash

[JHausmann]

I suspect the problem is that it's not null but a zero length string, which isn't necessarily the same.

[eshimano]

Thank you all for your help, this is the story:

a date field in access dosn't accept nulls or "". I tried your idea with an extra field and it works great on most of the fields, the only problem is that one of my forms has 66 date fields so there isn't much room for anything else.

What I did was change the fields in the database to string. then in vb I validated and formatted them.

In crystal reports when I needed to do calculations, sorting etc.. with the date field I converted it from string to date.

It is not the best solution but since everything works great now I am happy.

Thank's again for your help.
S.T.