3184 on SQL Insert

[Harrison]

I am using a Insert to add data to a table. If one of the fields is Null
(Empty) I get this error... 3184.

I can use the form and leave [ToEmployeeID] or [ToDepartmentID] Null (Empty)
and not an issues. Its something with SQL. My table data is long integers.
My Debugger window show this:
: strSQL : "INSERT INTO Transferred (QuestionID, EmployeeID, DepartmentID,
CustomerID, CallCodeID, Status, Severity ) VALUES ( 11, 6, , 8, 1, 1, 1): " :
String

How can I get this code execute.

strSQL = "INSERT INTO Transferred (QuestionID, EmployeeID, "
strSQL = strSQL & "DepartmentID, CustomerID, "
strSQL = strSQL & "CallCodeID, Status, Severity ) "
strSQL = strSQL & "VALUES ( "
strSQL = strSQL & [Forms]![frmStartUp]![QuestionID].Value
strSQL = strSQL & ", " & [Forms]![frmTransfer]![ToEmployeeID].Value
strSQL = strSQL & ", " & [Forms]![frmTransfer]![ToDepartmentID].Value
strSQL = strSQL & ", " & [Forms]![frmStartUp]![CustomerID].Value
strSQL = strSQL & ", " & [Forms]![frmStartUp]![CallCode].Value
strSQL = strSQL & ", " & [Forms]![frmStartUp]![Status].Value
strSQL = strSQL & ", " & [Forms]![frmStartUp]![Severity].Value
strSQL = strSQL & ") "

 

[Harrison]

Sorry, the correct number error code number is 3134.

 

[Van T. Dinh]

I am not sure whether this is necessary or not but I normally spell out Null
in the Values list rather than leaving it empty in the SQL String. That
works fine for me to date.

 

[Douglas J. Steele]

As Van says, you either need to include Null as the value in the Values
section:

INSERT INTO Transferred (QuestionID, EmployeeID, DepartmentID, CustomerID,
CallCodeID, Status, Severity ) VALUES ( 11, 6, Null, 8, 1, 1, 1)

or you leave the field out of the Field list (and don't supply a value at
all)

INSERT INTO Transferred (QuestionID, EmployeeID, CustomerID, CallCodeID,
Status, Severity ) VALUES ( 11, 6, 8, 1, 1, 1)

 

[Harrison]

Douglas,

I changed my code to change the value to a "0" and this displays the same
strSQL of
The Immediate window displays this....
INSERT INTO Transferred (QuestionID, EmployeeID, DepartmentID, CustomerID,
CallCodeID, Status, Severity ) VALUES ( 11, 6, , 8, 1, 1, 1)
?IsNull(ToEmployeeId)
False
?IsNull(ToDepartmentId)
True

The Local windows displays this....
: strSQL : "INSERT INTO Transferred (QuestionID, EmployeeID, DepartmentID,
CustomerID, CallCodeID, Status, Severity ) VALUES ( 11, 6, 0, 8, 1, 1, 1) " :
String

My code is this...
strSQL = "INSERT INTO Transferred (QuestionID, EmployeeID, "
strSQL = strSQL & "DepartmentID, CustomerID, "
strSQL = strSQL & "CallCodeID, Status, Severity ) "
strSQL = strSQL & "VALUES ( "
If IsNull([Forms]![frmTransfer]![ToEmployeeID].Value) Then
[Forms]![frmTransfer]![ToEmployeeID].Value = 0
End If
If IsNull([Forms]![frmTransfer]![ToDepartmentID].Value) Then
[Forms]![frmTransfer]![ToDepartmentID].Value = 0
End If
strSQL = strSQL & [Forms]![frmStartUp]![QuestionID].Value
strSQL = strSQL & ", " & [Forms]![frmTransfer]![ToEmployeeID].Value
strSQL = strSQL & ", " & [Forms]![frmTransfer]![ToDepartmentID].Value
strSQL = strSQL & ", " & [Forms]![frmStartUp]![CustomerID].Value
strSQL = strSQL & ", " & [Forms]![frmStartUp]![CallCode].Value
strSQL = strSQL & ", " & [Forms]![frmStartUp]![Status].Value
strSQL = strSQL & ", " & [Forms]![frmStartUp]![Severity].Value
strSQL = strSQL & ") "
'strSQL = strSQL & "WHERE
(((Question.QuestionID)=[Form]![frmStartUp]![QuestionID]));"

Now the record is added. New issue. The record is added in two parts.
There is a record for EmployeeID or DepartmentID which ever was choosen then
a new record for the rest of the fields. Don't know why its seperating this
statement into two records instead of one ?

Again thanks

Harrison

As Van says, you either need to include Null as the value in the Values
section:

INSERT INTO Transferred (QuestionID, EmployeeID, DepartmentID, CustomerID,
CallCodeID, Status, Severity ) VALUES ( 11, 6, Null, 8, 1, 1, 1)

or you leave the field out of the Field list (and don't supply a value at
all)

INSERT INTO Transferred (QuestionID, EmployeeID, CustomerID, CallCodeID,
Status, Severity ) VALUES ( 11, 6, 8, 1, 1, 1)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I am using a Insert to add data to a table. If one of the fields is Null
(Empty) I get this error... 3184.

I can use the form and leave [ToEmployeeID] or [ToDepartmentID] Null
(Empty)
and not an issues. Its something with SQL. My table data is long
integers.
My Debugger window show this:
: strSQL : "INSERT INTO Transferred (QuestionID, EmployeeID, DepartmentID,
CustomerID, CallCodeID, Status, Severity ) VALUES ( 11, 6, , 8, 1, 1, 1):
" :
String

How can I get this code execute.

strSQL = "INSERT INTO Transferred (QuestionID, EmployeeID, "
strSQL = strSQL & "DepartmentID, CustomerID, "
strSQL = strSQL & "CallCodeID, Status, Severity ) "
strSQL = strSQL & "VALUES ( "
strSQL = strSQL & [Forms]![frmStartUp]![QuestionID].Value
strSQL = strSQL & ", " & [Forms]![frmTransfer]![ToEmployeeID].Value
strSQL = strSQL & ", " & [Forms]![frmTransfer]![ToDepartmentID].Value
strSQL = strSQL & ", " & [Forms]![frmStartUp]![CustomerID].Value
strSQL = strSQL & ", " & [Forms]![frmStartUp]![CallCode].Value
strSQL = strSQL & ", " & [Forms]![frmStartUp]![Status].Value
strSQL = strSQL & ", " & [Forms]![frmStartUp]![Severity].Value
strSQL = strSQL & ") "