Inserting Null values

[PO]

I use the following sql to insert data from a web page into an Access 2003
table:

cnLOKAL.Execute "INSERT INTO [Revenue] " & _
"VALUES('" & rs.Fields("YEAR").Value & "'," & _
rs.Fields("01").Value & "," & _
rs.Fields("02").Value & "," & _
rs.Fields("03").Value & "," & _
rs.Fields("04").Value & "," & _
rs.Fields("05").Value & "," & _
rs.Fields("06").Value & "," & _
rs.Fields("07").Value & "," & _
rs.Fields("08").Value & "," & _
rs.Fields("09").Value & "," & _
rs.Fields("10").Value & "," & _
rs.Fields("11").Value & "," & _
rs.Fields("12").Value & ")"

The YEAR field is of text type and the fields 01 through 12 are of currency
type. The sql works as log as all the currency fields contain numbers but
sometimes one of the fields contains a null value. When that happens an
error occurs. What do I need to do to be able to insert the null values into
the table?

Regards
PO

 

[Ken Sheridan]

Call the Nz function to return a zero in place of the NULL, e.g.

Nz(rs.Fields("01").Value,0)

Normally a column of currency data type would have a DefaultValue of zero
and a Required property of True (equivalent to the NOT NULL constraint in
DDL). NULLs are usually inadvisable in a currency column because of their
semantic ambiguity, and the fact that they propagate in arithmetic
expressions, i.e. any arithmetic expression which includes a NULL will
evaluate to NULL regardless of the other values in the expression.

Ken Sheridan
Stafford, England