asked on

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:}

Regards
Tony

bkthompson2112

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.

ASKER CERTIFIED SOLUTION

PePi

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

pitwood

ASKER

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
Else
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

ten:
Debug.Print Err, Err.Description
End Sub

This appears to work OK but when I hit the command

datPrimaryRS.Recordset.Update

it comes back with "MultiStep operation generated errors.

This is what I'm battling with.

Regards
Tony

P.S. Maybe I should raise it as another question as I have already awarded the points?