Problem w/INSERT INTO statement

[Laurel]

I have a bit of code designed to copy most of the values of the current
record in my form into the table, just changing the keys. It looks like
this, and works fine unless one of the values is a null. In that case, the
ls_SQL variable would look like this if Social_Skills was null:

INSERT INTO tblScores(Student_ID,....) VALUES ( 10,#10/10/2003#,3,3,,2)

When this happens, I get a "Syntax error in INSERT INTO statement." at the
Execute command. I do need to preserve the null values in the new row.
Nulls are meaningful. Can anyone tell me the proper syntax to insert nulls
this way?

This is the code.
ls_Sql = "Insert into
tblScores(Student_ID,Score_Date,Period_Code,Safety,Social_Skills,Level_Fines
)" _
& " VALUES (" & ai_StudentID _
& ", #" & afrm_Form![Score_Date] & "#," _
& ai_Period & "," _
& afrm_Form!Safety & "," _
& afrm_Form!Social_Skills & "," _
& afrm_Form!Level_Fines & ")"

Call CurrentDb.Execute(ls_Sql, dbFailOnError)

 

[John W. Vinson]

When this happens, I get a "Syntax error in INSERT INTO statement." at the
Execute command. I do need to preserve the null values in the new row.
Nulls are meaningful. Can anyone tell me the proper syntax to insert nulls
this way?

You need to have the literal word NULL in the VALUES() argument:

This is the code.
ls_Sql = "Insert into
tblScores(Student_ID,Score_Date,Period_Code,Safety,Social_Skills,Level_Fines
)" _

& " VALUES (" & ai_StudentID _
& ", #" & afrm_Form![Score_Date] & "#," _
& ai_Period & "," _
& afrm_Form!Safety & "," _
& NZ(afrm_Form!Social_Skills,"NULL") & "," _
& afrm_Form!Level_Fines & ")"