Clearing a date field

Greetings, all,

Using an SQL database, I am having trouble clearing a datefield.  If I highlight and delete the existing date, I get an Invalid Date Format error.

I have tried doing it in code by making it    = ""  and also trying   = NULL   and   = <NULL>  but nothing seems to work.

My son-in-law reckons the best way to do it is to store it in a text field, but I'm sure there is a (very) simple answer . . .   o:}

Who is Participating?
PePiConnect With a Mentor Commented:
does the date field allow nulls?

if so,  a simple update statement can clear the field.


update your_table set date_field = Null

Are you trying to do this from code or from the database?

Using Enterprise Manager for SQL Server, you can press Ctrl+0.
That will set the field to Null.
pitwoodAuthor Commented:
Hi PePi

Thanks for your answer - it works well in code as in this example  

    If Len(fg2.TextMatrix(fg2.Row, 2)) < 1 Then
       DataEnvironment1.rsBldRemarks!DateDue = Null
       DataEnvironment1.rsBldRemarks!DateDue = fg2.TextMatrix(fg2.Row, 2)
    End If

but if I try to change a text box linked to the database as follows, it doesn't work too well

 Private Sub Text10_LostFocus()
  On Error GoTo ten
  If Len(Text10) = 0 Then datPrimaryRS.Recordset!VacatingDate = Null
Exit Sub

  Debug.Print Err, Err.Description
End Sub

This appears to work OK but when I hit the command


it comes back with "MultiStep operation generated errors.

This is what I'm battling with.


P.S. Maybe I should raise it as another question as I have already awarded the points?
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.