3184 on SQL Insert

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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 & ") "
 
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.
 
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)
 
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


Douglas J. Steele said:
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!)



Harrison said:
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 & ") "
Click to expand...
Click to expand...
 
Back
Top