reset a date field to null

[dfeigen115]

I have a table which through normal processing updates a date field to a
value entered on a form. Periodically, this date field may need to be reset
to deleted or set to NULL (preferably). This "reset" would be done via the
same form, by "deleting" the date value.

the update populating the date field (or changing it to a new date) works.
I'm unable to reset it to a "null" or blank value. In the code below,
MPMImpPlanDate is the date field that is beign reset to "null". A copy of the
update statement is below:

dim mpmimp as date

if isnull(Me.MPMImpPlanDt) then
mpmim=null
else
mpmimp = FormatDateTime(Me.MPMImpPlanDt, vbShortDate)
end if
strSQL0 = "UPDATE cacrdescription SET cacrstatus = 'implementing',
mpmimpplanwho = '" & Me.MPMImpPlanWho & "', mpmimpplandt = '" & mpmimp & "'
where (Cacrdescription.CACRNumber = " & Me.CACR_number & ");"

any suggestions/recommendations would be appreciated.
thansk, Dan

 

[Allen Browne]

You need to contatenate the literal word Null into the string if the value
is null. If it is not null, concatenate the value inside the correct
delimiters.

This kind of thing:

If Not IsNull(Me.CACR_number) Then
strSQL0 = "UPDATE cacrdescription SET cacrstatus = 'implementing',
mpmimpplanwho = " &
IIf(IsNull(Me.MPMImpPlanWho, "Null", """" & Me.MPMImpPlanWho & """") & ",
mpmimpplandt = " &
IIf(IsNull(Me.MPMImpPlanDt, "Null", Format(Me.MPMImpPlanDt, "\#mm\/dd\/yyyy
hh\:nn\:ss\#")) &
" WHERE (Cacrdescription.CACRNumber = " & Me.CACR_number & ");"

Debug.Print strSQL0
End If

BTW, your existing code may have been failing before it ever got to the
string, on this line:
mpmim=null
Since mpmimp is declared as a Date type, it cannot be Null. Only the Variant
can be null in VBA code. That's #4 in this article:
Common Errors with Null
at:
http://allenbrowne.com/casu-12.html

 

[dfeigen115]

Thanks Allen, I'll review and try your recommendation.
Dan