INSERT query with *possible* null variables.

[jakeisstoked]

Hi, I have this query being executed in VBA, it's a long insert query and it's getting the data from a form, problem is not all of the fields are required, and I will get a "data type mismatch in expression" exception if there are any null values.

With a query that isn't running from code this isn't a prob because Access just pops up an error message asking if you wan't the fields to have null values, but the exact same query from VB has the error, and wont continue (obviously I can ignore the exception, but then there's no insert).

eg
"INSERT INTO table (field1, field2, field2, etc...) VALUES
('" & variable1 & "', '" & variable2 & "', '" & variable3 & "', '" & etc... & "');"

so if any one of the variables is blank then it screws up. But I NEED to allow for different combos of blanks and I NEED this query running from code. I've tried many combinations of variables and quotes, but I'm stuck.

Is this possible?
-Jake

 

[Golom]

Real question is If a variable is blank (or NULL) then what do you want inserted in the target table?

If inserting blanks is acceptable then

"INSERT INTO table (field1, field2, field2, etc...) VALUES
('" & IIF(IsNull(variable1), " ", variable1) & "', '" & 
      IIF(IsNull(variable2), " ", variable2) & "', '" & 
      IIF(IsNull(variable3), " ", variable3) & "', '" & 
      etc... & "');"

If you don't want to insert blanks then

SQL1 = "INSERT INTO table ("
If Len(Trim(Variable1)) > 0 Then 
   SQL1 = SQL1 & "Field1,"
   SQL2 = SQL2 & "'" & Variable1 & "',"
End If 
If Len(Trim(Variable2)) > 0 Then 
   SQL1 = SQL1 & "Field2,"
   SQL2 = SQL2 & "'" & Variable2 & "',"
End If 
' ... etc. ...
SQL1 = Left(SQL1, Len(SQL1) - 1)
SQL2 = Left(SQL2, Len(SQL2) - 1)

SQL = SQL1 & ") VALUES (" & SQL2 & ")"

and you obviously need some code to check if there were no non-null values.

 

[jakeisstoked]

Hi, thanks, I see how that works, the second part was what I was after.
I actually came up with a much simpler solution, but you need 2 queries. It's easy to just run an insert query with all the required values, then run conditional update querie/s for every non required variable that isn't null. But it's inefficient.
Thanks.

-Jake

 

[PHV]

You may try something like this:
"INSERT INTO table (field1, field2, field2, etc...) VALUES
(" & Nz("'" + variable1 + "'","Null") & "," & Nz("'" + variable2 + "'","Null") & "," & Nz("'" + variable3 + "'","Null") & "," & etc... & ")"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886