runsql update fields from form error occurs when dates are null 1

[deb331]

I have a form based on a query joining two tables. The
tables can be updated individually - so I am using a loop to check for changes in either table. When I update I first write a history record and then update the table. To update a table I am using docmd.runsql update table1 set table1.date1= # me!date1#, etc. The problem occurs with a date on the form that is null. I am getting a syntax error on ##. If I enter a date on the form it updates the table successfully.
Also, the update statement is long because I have about 20
fields to update on one table. Is there a way around this?
Thanks

 

[shmiller]

What about something like this:

if isnull(me!date1) or me!date1 = "" then
strDate = "null"
else
strDate = "#" & me!date1 & "#"
end if
strSQL = "update table1 set table1.date1=" & strDate
docmd.runsql strSQL


Or I think if you set the format properties of date1 to a date format, you don't need the # signs. but you still might have a problem with nulls so I would suggest you just check for a null or empty string, and then set up your sql string

Shanti

 

[deb331]

Shanti,
Thanks for the info
Debbie

 

[deb331]

I changed the format for the dates on the form to short date
and in the sql statement changed the # to '. It updated the Oracle tables even with null dates which is just what I wanted. Now I have a new problem- when I click on add on the form (to add a new record) all date fields display as 12/30/99.