Excel and SQL - Update Excel Table

Hi

I am trying, from a form, to update a table through SQL, with the following instructions:

Please Login or Register  to view this content.

Everything goes well except when Var2 is Null.
What was intended was that when Var2 is null, the table would be updated with a NULL value and not zero, it is as if you delete the value in a cell.

Is it possible to do that? How?

Thanks
Jorge

Hi,

Maybe just add an else condition to the below line, if it gets into else, then Set Var2 to Null (Not with Double Quotes), just Null.

Please Login or Register  to view this content.

This is just a guess, please try and let me know if it works..

Cheers!

It's combobox so, check if value's length is >0. And for numeric variable, you can't assign empty/null. It should be set to 0.
If you want to use null/empty you need to declare variable as variant.

Ex:

Please Login or Register  to view this content.

Then declare variable as variant. Double can't hold null. As VBA interprets empty numeric variable as Zero.

I.E.

Please Login or Register  to view this content.

You'll need to change SQL query syntax then.

When you "debug.print Sql" in code before the line that generates that error. What do you get?

I'm guessing you'll need to change from "Column = 'xxx'" syntax to "Column Is null" syntax when variable is null.

I assumed as much. But what does "Debug.Print Sql" return in immediate window?

I'm guessing that you see "Update SET ... ... [Table$].[Column] = ''," If you need to update column with null value. Syntax should be...
"Update Set ... ... [Table$].[Column] = NULL,".

Notice that single quotes are removed when setting column value to NULL. As null isn't a string data type.

So like I said. You need to change your SQL query construction when variable is null. Single quotes should be removed and use null.

Easiest way to do it is... instead of setting value to Empty. set it to "NULL" string.

It will construct your Sql as...
"UPDATE [BDBACK$] SET [BDBACK$].[idDF1] = 'NULL' , [BDBACK$].[PercDF1] = 'NULL' , ..."

Then you'd use Replace() to remove single quotes around NULL.
Ex:

Please Login or Register  to view this content.

Or just leave variables as is, and replace "''" with "NULL".

You are welcome and thanks for the rep