user2704742 user2704742 - 3 months ago 5
MySQL Question

Add case statement in insert query to turn empty string into null

I am trying to insert values from an Excel VBA userform to MySQLDBA.
The problem comes with empty text box values for date columns.
MySQL doesn't accept " but it allows Null.

Can I add a case statement in my insert query to check if the value for the column is empty string, and if it is, insert null for that column? Otherwise, it should insert the value of the textbox.

Here is my current query in VBA, where Date_of_Birth columns should be checked for empty string:

Str = "insert into sample.details (ID,Name,Age,Gender,Date_of_Birth) values ('" & txt1.Value & "', '" & txt2.Value & "','" & txt3.Value & "','" & txt4.Value & "','" & txt5.Value & "');"


P.S I tried setting a if condition for the txt5.value but that didn't work.

Answer

You can use the IIf function.

Str = "insert into sample.details (ID,Name,Age,Gender,Date_of_Birth) values ('" & txt1.Value & "', '" & txt2.Value & "','" & txt3.Value & "','" & txt4.Value & "'," & IIf(txt5.Value="","null","'" & txt5.Value & "'") & ");"