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
pitwoodAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

bkthompson2112Commented:
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.
0
PePiCommented:
does the date field allow nulls?

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

e.g.

update your_table set date_field = Null

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
    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?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.