Null Dates in SQL strings

[chrismcbride]

I have run up against this one a couple of times before and have never found a solution. Hopefully there is a wiser person than I who may have....
I want to copy the form's current values to a new record using a SQL Insert statement fired by a button on the form. I will have to trap for null values inside the SQL statment to ensure that there are no errors created during the insert. This is simple enough in the case of a value that is going to a text or number field in the destination table. I can simply substitute values using iif(isnull(value),0,value) or iif(isnull(value)," ",value). The problem is if there is a null value in a date field. I can not find a value that I can insert using SQL that does not create a Date type value and yet also does not create an error.
It is likely when I create this new copy of the record that I will have a null date and I want to keep that condition in the new record.
I have tried everything I can think of with no results! Any suggestions???
Chris

ps. - have tried...

strSql = "INSERT INTO tblWhatever (date_field) VALUES (#" & Null & "#);"

strSql = "INSERT INTO tblWhatever (date_field) VALUES (" & Null & ");"

strSql = "INSERT INTO tblWhatever (date_field) VALUES (#" & 0 & "#);"
-This one gives me a date that is meaningless to the record

strSql = "INSERT INTO tblWhatever (date_field) VALUES (#" & "" & "#);"

strSql = "INSERT INTO tblWhatever (date_field) VALUES (" & " " & ");"

and other variations that you may predict.

 

[ott]

Perhaps I'm misunderstanding what you are trying to do, but this works fine for me:

strSQL = "INSERT INTO tblWhatever( Date_Field )
SELECT Null AS NullExpression;