Setting a DateTime field back to "empty"

[ts_hunter]

I have several date fields on my form. Depending on what the user selects for options, certain date fields are set to 0 and disappear as they are not needed. However, if they change their mind, I want to make the date fields reappear and have their value go back to what it was when the form initially opened.

What is the trick to clear out the contents of a datetime field? Null does not work, 0 gives me 12:00:00am. I just want an empty box like my other untouched datetime fields.



Any ideas?

[ItsMe]

Depending on the purpose, I will use a default value like 1-1-1990. The date data type will store dates ranging from 1 January 100 to 31 December 9999.

[ts_hunter]

I tried that, but I am afraid the user will 'forget' to enter a valid date if I set it to 1/1/1900. That is why I want to set it to the value it has when the form initially opens, which is an empty box.

[Micron]

You don't say how the textbox gets its value after the form opens with no value being shown. Is the textbox bound? If not and you are using the Default Value property being equal to Date(), just set that value to "" in code. If it is bound, I think you can set it to "" even if the bound field is date/time data type. Regardless, if using a default date as suggested or "", you will have to code for the textbox being one of those values anyway to prevent using meaningless data, will you not?

[June7]

A textbox bound to date/time field cannot be set to empty string. Set it to Null.

Why does Null not work? What did you try? What happens?

[ts_hunter]

You don't say how the textbox gets its value after the form opens with no value being shown. Is the textbox bound? If not and you are using the Default Value property being equal to Date(), just set that value to "" in code. If it is bound, I think you can set it to "" even if the bound field is date/time data type. Regardless, if using a default date as suggested or "", you will have to code for the textbox being one of those values anyway to prevent using meaningless data, will you not?

The form is being opened in add mode (acFormAdd) because it is a new record. The text box is bound to a DateTime field in my datafile. Yes, I will have data validation when the user clicks "Done".

[ts_hunter]

FIGURED IT OUT!!!!!!

When I tried to set it to Null with the code below:

Me.AssetTransfer.Enabled = True
Me.AssetTransfer.Visible = True
Me.AssetTransfer.Value = Null

I get this error





I went back into SQL Server and changed my AssetTrasfer field to allow Nulls and now I can set it to Null and get an empty box. I will trap for users leaving it blank in my validation code under the "Done" Button. THANKS ALL!!!!

[Micron]

A textbox bound to date/time field cannot be set to empty string.

Well, in version 2007 it seems you can. I tested it first because I didn't think so, but when I cycled back and forth between the records with the navigation button, the date was gone after my command button code changed the control value to "". And yes, the table field is date/time, the form is bound to the table, and the control to the table field.
Also, poster said That is why I want to set it to the value it has when the form initially opens, which is an empty box which I reasoned meant that it was not bound, therefore it could be set to "". Looks like I can do it anyway.
Maybe I've overlooked something, but here's what I see after this runs and here is the field data type:
Private Sub Command12_Click()
Forms!form1.Text10 = ""
End Sub

[June7]

Ooops! I made an assumption because date/time is a number and number fields cannot have text and I always associated empty string with text type data. But apparently Access does something 'behind the scenes' with the empty string and actually results in Null field. I typed "" into date/time and number fields and the field is Null. I also experimented with typing spaces and that does not error, Access just drops leading/trailing spaces.

[ItsMe]

Back in the day I used to assign empty strings to text fields and then test for empty strings during data validation. Whatever it is that Access does nowadays blew all of that out the window for me. I did not know that assigning an empty string to a Date field or Date control would result in a Null. I prefer the old way.

[June7]

I don't allow empty strings in text fields. I always test for Null, regardless of field type.

[ItsMe]

I don't allow empty strings in text fields. I always test for Null, regardless of field type.

Not even for 5 minutes? Ideally, you would not have a Null or an empty string in a field. The point I was trying to make is that I found it easiest to write code that checked for "", 0, or 1-1-1990. This way I would not have to test for Null and "".

I guess now we do not have to test for both. However, I do believe that there is a way for the user to assign "" to a control before a full commit to the table. So if you are trying to validate before a commit, you are still challenged with testing both Null and "". My solution is to pass the value to a function as a Variant. The function will determine the Variant type and then apply logic based on the Variant type. Not a big fan of using expensive Variants but this seems to be the only solution.

[June7]

I will be more alert to issues with checking for Null but so far not run into any.