Problem updating tbl fields from possible null

[ccox]

I have a table named tblhistory that I have added a new field called FieldB. Either FieldA or FieldB can be null. One has to be null. I have the following code to update tblhistory with values entered on a form, but I am unsure how to make it accept nulls. it keeps giving me an error because there is no value for one of the fields. Can someone please help??

Me.Status = "statusname"
strSQL = " INSERT INTO tblHistory " _
& "([ID], [FieldA], [FieldB], [Status], [Date_of_Change], [End_Change_Date] ) VALUES (" _
& Forms![Form]![ID] & ", '" _
& Forms![Form]![FieldA] & "', " _
& Forms![Form]![FieldB] & "', " _
& "'" & Forms![Form]![Status] & "' , " _
& "#" & Forms![Form]![statusname_Date] & "# , " _
& "#" & Forms![Form]![Scr_End_Date] & "#);"


DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
'DoCmd.Close
DoCmd.SetWarnings True
End If

 

[martin.duplessi]

Howzit,

Not to suggest the obvious, but are FieldA and FieldB set to "Required" = "Yes" in the table/field setup? If so, then you will not be able to insert a record with either values = NULL.

 

[Jon K]

When a field is null, you need to insert the value of Null, not an empty string ''.

strSQL = " INSERT INTO tblHistory " _
& "([ID], [FieldA], [FieldB], [Status], [Date_of_Change], [End_Change_Date] ) VALUES (" _
& Forms![Form]![ID] & ", " _
& IIf(IsNull(Forms![Form]![FieldA]), "Null", "'" & Forms![Form]![FieldA] & "'") & ", " _
& IIf(IsNull(Forms![Form]![FieldB]), "Null", "'" & Forms![Form]![FieldB] & "'") & ", " _
& "'" & Forms![Form]![Status] & "' , " _
& "#" & Forms![Form]![statusname_Date] & "# , " _
& "#" & Forms![Form]![Scr_End_Date] & "#);"

.