Null in Insert Statement

[Guest]

I am getting an error statement when i try to insert a null field into a
query I have used some of the other posts to come up with the code, but it
still isnt working. Any help you can provide would be great.

SQL = "INSERT INTO qryCompanyInvolvements (CompanyID, CategoryID, Priority)
VALUES (" & Me.Parent.CompanyID & ", 1, " & IIf(IsNull(Me!cmbACMPriority),
Null, Me!cmbACMPriority) & ");"

Thanks!!

 

[Guest]

Hi

I think the problem is that you are trying to insert a Null value which, by
definition, cannot exist so cannot be manipulated/inserted.

Presumably, you just want the field to hold a 'nothing' value so why not use
an empty string - "".

Try: SQL = "INSERT INTO qryCompanyInvolvements (CompanyID, CategoryID,
Priority) VALUES (" & Me.Parent.CompanyID & ", 1, " &
IIf(IsNull(Me!cmbACMPriority), '"''", Me!cmbACMPriority) & ");"

Note that "''" is, exagerated, " ' ' " .

Cheers.

BW

 

[Guest]

Thanks it helped with my first problem, but now i have a asecond one. I used
the code in a similar spot. It says that it won't update because of a type
conversion failure. However when i check the table to see if the record has
been changed, it has. I didn't want to disable the warnings for this,
because i would like to alert the user that they are about to change this
record.

SQL = "UPDATE tblCompanyInvolvements SET tblCompanyInvolvements.Priority = "
& IIf(IsNull(Me!cmbFSPriority), "''", Me!cmbFSPriority) & " WHERE
(((tblCompanyInvolvements.CompanyID)=" & Me.Parent.CompanyID & ") AND
((tblCompanyInvolvements.CategoryID)=9));"

Thanks again for all of your help.

 

[Guest]

Different field type require different handeling in the SQL

Date - #
e.g
SQL = "Update TableName Set DateFieldName = #" & Variable & "#"

String - '
e.g
SQL = "Update TableName Set StringFieldName = '" & Variable & "'"

Number - Nuthing
e.g
SQL = "Update TableName Set DateFieldName = " & Variable

What is the type of the fields in your table?
If Priority is string the try

SQL = "UPDATE tblCompanyInvolvements SET tblCompanyInvolvements.Priority =
'" & IIf(IsNull(Me!cmbFSPriority), Null, Me!cmbFSPriority) & "' WHERE
(((tblCompanyInvolvements.CompanyID)=" & Me.Parent.CompanyID & ") AND
((tblCompanyInvolvements.CategoryID)=9));"

 

[John Spencer]

Remember you are building an SQL string. So try putting quotes around
NULL so it will be seen as NULL

SQL = "INSERT INTO qryCompanyInvolvements (CompanyID, CategoryID, Priority)
VALUES (" & Me.Parent.CompanyID & ", 1, " & IIf(IsNull(Me!cmbACMPriority),
"Null", Me!cmbACMPriority) & ");"

So the string will end up looking like the following, if cmbACMPriority
is Null and companyID is 32.

INSERT INTO qryCompanyInvolvements (CompanyID, CategoryID, Priority)
VALUES (32, 1, Null)



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================