none
Help in Excel command RRS feed

  • Question

  • Hi, I have 3 columns B3, C3 and D3 and every column can have NULL values. But I don't want NULL in commas like "NULL".

    I want to put condition that if any column has NULL value then it should not be in the commas. It should be without commas. Please help to put the condition in the command. 


    ="insert into customers values('" &B3 &"','" & C3 & "','"&D3&"');"

    insert into customers values('A','200','NULL');

    /*I want the following output*/

    insert into customers values('A','200',NULL);

    Wednesday, March 16, 2016 4:52 PM

Answers

  • Hi,

    Its look like you have a data in excel and from excel vba you are trying to insert data in access table.

    Try the following code.

    If cell contain nothing then it will add nothing to access field and if cell contain Null then it will add Null without “Null” in access table field.

    Sub Inserttbl() Dim var1 As String Dim var2 As String Dim var3 As String var1 = Range("A1:A1").Value var2 = Range("B1:B1").Value var3 = Range("C1:C1").Value Dim dbs As Database Set dbs = OpenDatabase("C:\Users\v-padee\Desktop\Database2.mdb") dbs.Execute " INSERT INTO Table1 " & "(fname,mname, lname) VALUES " & "('" & var1 & "','" & var2 & "','" & var3 & "');" dbs.Close

    End Sub

    Regards

    Deepak



    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place. <br/> Click <a href="http://support.microsoft.com/common/survey.aspx?showpage=1&scid=sw%3Ben%3B3559&theme=tech"> HERE</a> to participate the survey.

    • Marked as answer by Kenny_Gua Thursday, March 17, 2016 3:56 PM
    Thursday, March 17, 2016 5:37 AM
    Moderator
  • Option Explicit Const QUOTE As String = """" Sub test() Dim str As String Dim Sql As String Dim i As Integer Dim r As Range str = "" Set r = Range("B3") For i = 1 To 3 If IsEmpty(r.Value) Then str = str & vbNull & "," Else str = str & QUOTE & r.Value & QUOTE & "," End If Set r = r.Offset(0, 1) Next i Sql = "insert into customers values(" & str & ");" End Sub



    Best regards George

    • Marked as answer by Kenny_Gua Thursday, March 17, 2016 3:56 PM
    Thursday, March 17, 2016 1:54 PM
  • Hi, Kenny

    you can use the below mention line. it can doesn't add '' when it is Null in the cell and it will add '' when the value is different.

    ="insert into Table1 values( " & IF(B3="Null","Null","'"& B3 &"'") & "," & IF(C3="Null","Null","'"& C3 &"'") & "," & IF(D3="Null","Null","'"& D3 &"'") & ");"

    it is perfectly working.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place. <br/> Click <a href="http://support.microsoft.com/common/survey.aspx?showpage=1&scid=sw%3Ben%3B3559&theme=tech"> HERE</a> to participate the survey.

    Monday, March 21, 2016 8:54 AM
    Moderator

All replies

  • You need to elaborate on your requirements

    Assuming B3 is "Charles", C3 is empty ("null" as you say) and D3 is "Kenny", the resulting SQL string would be:

    INSERT INTO Customers VALUES ("Charles", "", "Kenny")

    That will work.


    Best regards George

    Wednesday, March 16, 2016 5:38 PM
  • Hi, Please see below data and desired result. C3 has NULL value and I want NULL without quotation in INSERT statement. So if any column has NULL then result should show without quotation.

    ="insert into customers values('" &B3 &"','" & C3 & "','"&D3&"');" (I want condition here for all columns)

    B3         C3         D3
    Charles NULL Kenny insert into customers values('Charles',NULL,'Kenny');

    --Desired output (No quotation if value is NULL)

    insert into customers values('Charles',NULL,'Kenny');

    Wednesday, March 16, 2016 6:02 PM
  • Ok, how do you extract a null value from Excel? It's a string (or date, number .. etc) value. Null is an invention to describe a non-defined value in some programatically situations. Excel will never give you a "null" value as a string, only an empty string. Which fits perfectly into the SQL statement!


    Best regards George


    Wednesday, March 16, 2016 6:10 PM
  • Hi, Value in B3,C3,D3 column could be numeric, string or NULL. So if any column has NULL then result should show without quotation otherwise every result show within quotation.

    ="insert into customers values('" &B3 &"','" & C3 & "','"&D3&"');" (I want condition here for all columns)

    --Desired output (No quotation if value is NULL)

    insert into customers values('Charles',NULL,'Kenny');

    OR

    insert into customers values('100',NULL,'Kenny');

     
    Wednesday, March 16, 2016 6:30 PM
  • Hi, Value in B3,C3,D3 column could be numeric, string or 'NULL'. So if any column has 'NULL' then result should show without quotation otherwise every result show within quotation.

    ="insert into customers values('" &B3 &"','" & C3 & "','"&D3&"');" (I want condition here for all columns)

    --Desired output (No quotation if value is 'NULL' in any column)

    insert into customers values('Charles',NULL,'Kenny');

    OR

    insert into customers values('100',NULL,'Kenny');

    Wednesday, March 16, 2016 6:38 PM
  • Hi,

    Its look like you have a data in excel and from excel vba you are trying to insert data in access table.

    Try the following code.

    If cell contain nothing then it will add nothing to access field and if cell contain Null then it will add Null without “Null” in access table field.

    Sub Inserttbl() Dim var1 As String Dim var2 As String Dim var3 As String var1 = Range("A1:A1").Value var2 = Range("B1:B1").Value var3 = Range("C1:C1").Value Dim dbs As Database Set dbs = OpenDatabase("C:\Users\v-padee\Desktop\Database2.mdb") dbs.Execute " INSERT INTO Table1 " & "(fname,mname, lname) VALUES " & "('" & var1 & "','" & var2 & "','" & var3 & "');" dbs.Close

    End Sub

    Regards

    Deepak



    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place. <br/> Click <a href="http://support.microsoft.com/common/survey.aspx?showpage=1&scid=sw%3Ben%3B3559&theme=tech"> HERE</a> to participate the survey.

    • Marked as answer by Kenny_Gua Thursday, March 17, 2016 3:56 PM
    Thursday, March 17, 2016 5:37 AM
    Moderator
  • Hi, I have data in EXCEL and want formula in EXCEL. My formula is in E4. 

    Value in B3,C3,D3 column could be numeric, string or 'NULL'. So if any column has 'NULL' value then result should show without quotation otherwise every result show within quotation. I have some data with 'NULL' not ''. Need help in formula.

    E4 column: ="insert into customers values('" &B3 &"','" & C3 & "','"&D3&"');" (I want condition here for all columns)

    --Desired result from E4 (No quotation if value is 'NULL' in any column)

    insert into customers values('Charles',NULL,'Kenny');


    Thursday, March 17, 2016 1:00 PM
  • What is the database you're using? MS Access, MySQL or SQL Server? They handle NULL values differently.

    Best regards George

    Thursday, March 17, 2016 1:16 PM
  • Hi, My question if just for the following formula. I want to add formula in the following EXCEL Insert statement. So if any column (B3,C3,D3) has 'NULL' value then result should show without quotation otherwise every result show within quotation. I have some data with 'NULL' . 

    E4 column: ="insert into customers values('" &B3 &"','" & C3 & "','"&D3&"');" 

    Thursday, March 17, 2016 1:24 PM
  • Option Explicit Const QUOTE As String = """" Sub test() Dim str As String Dim Sql As String Dim i As Integer Dim r As Range str = "" Set r = Range("B3") For i = 1 To 3 If IsEmpty(r.Value) Then str = str & vbNull & "," Else str = str & QUOTE & r.Value & QUOTE & "," End If Set r = r.Offset(0, 1) Next i Sql = "insert into customers values(" & str & ");" End Sub



    Best regards George

    • Marked as answer by Kenny_Gua Thursday, March 17, 2016 3:56 PM
    Thursday, March 17, 2016 1:54 PM
  • Hi,

    I want the condition in the following EXCEL Insert statement but this command has error. Can you please fix this?

    ="insert into customers values(IF(B3=NULL,"NULL",'" &B3&"',C3=NULL,"NULL",'" &B3&"',C3=NULL,"NULL",'" &B3&"'))

    Thursday, March 17, 2016 2:24 PM
  • Kenny,

    With all respect, I'm not sure you understand what you are asking here.

    Several people have given you proposed answers, yet you ignore them. Instead you are following a route that does not work.

    Please look at the answers offered to you.


    Best regards George

    Thursday, March 17, 2016 3:20 PM
  • Hi, Kenny

    you can use the below mention line. it can doesn't add '' when it is Null in the cell and it will add '' when the value is different.

    ="insert into Table1 values( " & IF(B3="Null","Null","'"& B3 &"'") & "," & IF(C3="Null","Null","'"& C3 &"'") & "," & IF(D3="Null","Null","'"& D3 &"'") & ");"

    it is perfectly working.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place. <br/> Click <a href="http://support.microsoft.com/common/survey.aspx?showpage=1&scid=sw%3Ben%3B3559&theme=tech"> HERE</a> to participate the survey.

    Monday, March 21, 2016 8:54 AM
    Moderator