What is the best way to insert Date/Null into a Date Field?

[babakanoush]

What is the best way to insert Date/Null into a Date Field?

INSERT INTO MyTable (MyDate ) " _
& "VALUES ( “ & MyDate & “)”

MyDate could be a valid date or Null. I know about “#” sign, but the problem is when MyDate is Null then I get an error! I have few date fields on my form so I like to construct one Insert Statement to take care of all the possible cases. Thanks gurus.

 

[ShiftyJaffa]

I've had this problem before and just used an IF statement.

if isdate(nz(MyDate)) = True then
   docmd.runsql "INSERT INTO MyTable (MyDate ) " & "VALUES ( Cdate('“ & MyDate & “'))”
   else
   docmd.runsql "INSERT INTO MyTable (MyDate ) VALUES (Null)”
   end if

Easy way out I guess, there probably is a sweeter way, so perhaps someone else can shed some light on it.

Paul