INSERT INTO fails if inserting a variable with the value of Null

Question

The SQL INSERT INTO will fail if the variable I am inserting has the value of Null.

The variable costLab is Variant data type. The non-Null value would be Decimal.

db.Execute ("INSERT INTO budget 
(wbsid, category, capture_date, budget_date, budget_type, month_value) " & _
"VALUES ('" & wbsid & "', 'Labor', #" & importDate & "#, 
#" & monthDate & "#, 'Forecast', " & costLab & ");")

I want to insert Null if the code that sets costLab returns Null. But if a value is returned I want to insert that value. Obviously I could write an If statement that checks for null then insert "Null" directly but I wanted to know if there was a way without the If statement to insert Nulls via a variable.

Answer 1

You could use Nz() when you build the INSERT statement. It's similar to the IIf() approach, but is slightly more concise.

Dim strInsert As String
strInsert = "INSERT INTO budget (wbsid, category, capture_date, budget_date, budget_type, month_value) " & _
        "VALUES ('" & wbsid & "', 'Labor', #" & importDate & "#, #" & monthDate & "#, 'Forecast', " & Nz(costLab, 'Null') & ");"
Debug.Print strInsert ' <- examine the finshed statement in Immediate window
db.Execute strInsert, dbFailOnError

Debug.Print gives you an opportunity to examine the finished statement you give to the db engine to execute. In case of trouble, you can go to the Immediate window (Ctrl+g) to view the statement text. You can also copy that text and paste it into SQL View of a new query for testing.