Can't insert a null value

[Laurel]

Even though the table tblScores allows nulls in Contract, and has a lot of
rows with null, when li_contract, below is set to Null, the SQL is invalid.
The error message shows the SQL looks like this.

Insert into
tblScores(Student_ID,Score_Date,Period_Code,Safety,On_Task,Respect,Language,
Transition,Homework,Contract) VALUES (64,
#10/26/2004#,'L',5,5,5,5,5,Null,5)

And this is the error message
Invalid use of Null

Dim li_contract as integer

If ls_temp = Null Or ls_temp = "" Then
li_contract = Null
Else
li_contract = ai_value
End If

ls_sql = "Insert into tblScores(Student_ID,Score_Date,Period_Code" _
&
",Safety,On_Task,Respect,Language,Transition,Homework,Contract) " _
& " VALUES (" & !Student_ID _
& ", #" & adt_date & "#," _
& "'" & lrst_Periods!Period_Code & "'," _
& ai_value & "," _
& ai_value & "," _
& ai_value & "," _
& ai_value & "," _
& ai_value & "," _
& ls_homework & "," _
& li_contract & ")"

 

[Randy Harris]

I don't believe that an integer data type can hold a null. You have dimmed
li_contract as integer, then attempt to assign it a null value.

 

[Dan Artuso]

Hi,
Declare it as a variant if you want to make it null.

 

[Marc]

<snip>
Hi
Not sure if this is a typo, but the null

Even though the table tblScores allows nulls in Contract, and has a lot of
rows with null, when li_contract, below is set to Null, the SQL is
invalid.
The error message shows the SQL looks like this.

Insert into
tblScores(Student_ID,Score_Date,Period_Code,Safety,On_Task,Respect,Language,
Transition,Homework,Contract) VALUES (64,
#10/26/2004#,'L',5,5,5,5,5,Null,5)

here refers to homework not contract - can homework have null
Marc

 

[Laurel]

Thanks to all three of you. (I somehow mis-sent this earlier, and it didn't
show up in this thread.)

li_contract = Null

It looks like the error trapping was misleading. This was the line of code
that was generating the "invalid use of null."