I have a form which contains a date field. After the user has entered the relevant information on the form I use some sql to run an append query to write some of these fields to another table. One of these is this date field.

However, inputting the date on this form is not mandatory so sometimes when the sql string is run I am trying to append the field in the other table with a null date value and I get an error. I want to be able to do this, here is a section of my sql string, what do I need to change here so it will append a date with a null value


", #" & Me!Date & "#..........

Can you just modify the sql string based on if the date fld is null?

???
kh

Ken,

I can do that but what do I set the date to if it is null? I still want the date field in the new table to be a null value. The following does not work


... #" & IIf(IsNull(Me!Date), Null, Me!Date) & "# ...


I get run-tiome error 3075

"Syntax error in date in query expression '##'."

I was just thinking that you could leave the date pc out of the sql statement entirely if the date fld on the form was blank.

???
ken

I'm confused. You have a date field. In comes an update record. It might have a date, but might be null. The original field might have a date but might be null. If you wanted to load a null with a null, just do nothing - 'cause you ALREADY have a null, don't you? Just don't update at all when the IsNull([field]) is TRUE.