UPDATEing null dates

[davidfox]

I've got a few questions on storing dates in an MDB using SQL UPDATE...

(1) I would prefer to have empty date fields really be empty (rather than some unusal date like January 1, 0001 or whatever).

If a textbox.text equals "", what would I set the corresponding field in the database to? Some sort of Null value..?

I tried setting it equal to a DBNull value, but it didn't like that. It said, "Parameter?_10 has no default value."


(2) If a user types garbage into a text box that is supposed to hold a date, CDate(thatTextBox.text) complains loudly.

Is there a cleaner way to test the contents of the text box for valid date than to allow CDate() to attempt the conversion and then clean up the mess afterwards? Maybe something like a IsValidDate("10/11/2001") that would return a true or false.


Here is some code to put this question into context. The Null part does not work.

Dim Null as System.DBNull
[...]
' Prepare Paramater [HireDate]
pm = New OleDb.OleDbParameter
pm.OleDbType = OleDb.OleDbType.DBDate
pm.Value = iif (isdate(cdate(txtHireDate.Text),cdate(txtHireDate.Text), Null)))
dbCmd.Parameters.Add(pm)

[...]

update personnel set [Lastname]=?, [Firstname]=?, [HireDate]=? where [ssno]=?

 

[Pat Hartman]

Why are you using the .text property? In Access VBA, the .text property is only available at a specific point in time. You should probably be using the .value function.

Use the IsDate() function to determine if a field is a valid date.