Update Record to contain empty date

[marabak]

Hey

I have a query which I have built up in VBA to allow a quick and easy way for the user to edit a record (without having to delve into the tables etc.). Fundamentally, the data from that record is retrieved to a bunch of textboxes on a form, with a button to "save" the changes. The user literally just edits the texboxes and clicks save, with VBA SQL saving the changes.

I am having problems with the removing dates from the table. I had planned that the user would just delete the date from the textbox and the table would be updated accordingly. There is also the possibility that the date could be edited as well, so I can't just assume that I can set the record field to null (if that is even possible).

My code is currently..
'construct sql
addSQL = "UPDATE OnSite SET SerialNo = '" & editserial & "', Activity = " & editactivity _
& ", RefDate = #" & Me!editRefDate & "#, ExpDate = #" & Me!EditExpiry & "#" _
& ", Used = " & CheckUsed & ", UsedDate = #" & EditUsedDate & "#" _
& ", Disposed = " & CheckDisposed & ", DisposedDate = #" & EditDisposedDate & "#" _
& " WHERE [ID] = " & ID & ";"

DoCmd.RunSQL (addSQL)

The issue is that is the textbox is empty (EditUsedDate or EditDisposedDate), then the SQL string tried to set the record field to ## and I get a syntax error.

I feel like maybe I can construct different strings based on the values of the checkboxes, but this could get quite messy, so I am wondering if there is a cleaner, more efficient solution.

Any suggestions? Thank you

 

[Minty]

Add some code to check for null values in your date fields and construct the date string accordingly something like
If IsNull(Me.txtDate) Then

 

[arnelgp]

addSQL = "UPDATE OnSite SET SerialNo = '" & editserial & "', Activity = " & editactivity _
& ", RefDate = " & _
IIF(Me!EditRefDate & "" = "", "Null", "#" & Me!editRefDate & "#") & _
", ExpDate = " & _
IIF(Me.EditExpiry & "", "Null", #" & Me!EditExpiry & "#") &  _
", Used = " & CheckUsed & ", UsedDate = " & _
IIF(EditUsedDate & "" = "", "Null", "#" & EditUsedDate & "#") &  _
", Disposed = " & CheckDisposed & ", DisposedDate = " & _
IIF(EditDisposedDate & "" = "", "Null", #" & EditDisposedDate & "#") &  _
" WHERE [ID] = " & ID & ";"

 

[marabak]

Cheers guys, that seems like an elegant and simple solution