SQL Update with blanks

[Tyork]

I have an update statement that updates a table based on form entries. The entries are a combination of text values and dates - 11 in total. The form is unbound. The statement executes properly when all form fields are filled in. If an entry is left blank I'm thrown a syntax error:



Run-time error '3075'
Syntax error in date in query expression '#'.

The code is as follows:

 
SQLUpdate = "UPDATE EMS_Main SET DSP3Date = #" & Me!DSP3Date & "#, DSP3Result = '" & Me!DSP3Result & "', DSP5Date = #" & Me!DSP5Date & "#, DSP5Result = '" & Me!DSP5Result & "', BCDate = #" & Me!BCDate & "#, BCResult = '" & Me!BCResult & "', BackgroundNumber = '" & BackgroundNumber & "', OFACDate = #" & Me!OFACDate & "#, OFACResult = '" & Me!OFACResult & "', FDate = #" & Me!FDate & "#, FResult = '" & Me!FResult & "' WHERE EmployeeName = '" & Employee & "'"

I'm not sure how to code for blank entries. My only thought is that I can write something to dynamically build the sql statement based upon what entries are made. I'd rather not do that though.

Any help would be appreciated.

[weekend00]

you need to check nulls one by one ( you may not need to check nulls for text fields if you want update them to "" if nothing is in the textbox control.):
SQLUpdate = "UPDATE EMS_Main SET DSP3Result = '" & Me!DSP3Result & "', DSP5Result = '" & Me!DSP5Result & "', BCResult = '" & Me!BCResult & "', BackgroundNumber = '" & BackgroundNumber & "', OFACResult = '" & Me!OFACResult & "', FResult = '" & Me!FResult & "' "
if not isnull(Me!DSP3Date) then SQLUpdate= SQLUpdate & ", DSP3Date = #" & Me!DSP3Date & "# "
if not isnull(Me!DSP5Date) then SQLUpdate= SQLUpdate & ", DSP5Date = #" & Me!DSP5Date & "# "
if not isnull(Me!BCDate) then SQLUpdate= SQLUpdate & ", BCDate = #" & Me!BCDate & "# "
if not isnull(Me!OFACDate) then SQLUpdate= SQLUpdate & ", OFACDate = #" & Me!OFACDate & "# "
if not isnull(Me!FDate) then SQLUpdate= SQLUpdate & ", FDate = #" & Me!FDate & "# "
SQLUpdate= SQLUpdate & " WHERE EmployeeName = '" & Employee & "'"