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.

Incunabulum

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.

Tressatressia

Thanks Hans for the quick response. I am still wondering why exactly it is failing in the first place. For example an insert using a SELECT based on a table with a field value of null has no issues whatsoever. – Incunabulum

Examine the output from Debug.Print. My guess is when costLab is Null, the last part of the statement built from your original code will be this ... 'Forecast', ); – Tressatressia

With the code I suggested, the last part of the statement will end up as this when costLab is Null ... 'Forecast', Null); – Tressatressia

Hans you are correct, the root of my error is that I am creating a string. Therefore a variable with the value of Null will not automatically concatenate the string "Null". Thanks again for helping me work through this one. – Incunabulum

Answer 2

Just literally insert "null" when costLab is Null.

So, when you concatenate the SQL string, instead of the variable costLab, you just insert this:

IIf(IsNull(costLab), "null", costlab)

The complete query:

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

---

I know...technically, this is an If statement (IIf is just a short form of If...Then...Else), but it's the shortest form that I can think of.

Ury

Thanks for the quick response Christian. This is a valid workaround and I understand that, what I do not understand is why I cannot insert the value null simply using the variable. Why does it fail? – Incunabulum