Read table without blank rows to Insert to other table using vba

Question

I am Using Access VBA to read from a temp table and then inserting those values to another table, But i am getting blank rows while Inserting. I need a way to remove the blank rows.

  Table A        Table B
  A B C D         ID B
  1 1 1 1         1  1
  1 1   1         2  1
  1     1         3

**Expected Result**
    Table B
     ID  B
     1   1
     2   1

i am Inserting column B from Table A to Table B but i am getting one blank row which is not required

INSERT INTO TABLE_B(B) SELECT B FROM TABLE_A WHERE B is not null;"

Does that third "blank" row in table A really have a null B value, or could it be empty string? — Tim Biegeleisen
i tried this sql code too but getting same result INSERT INTO TABLE_B(B) SELECT B FROM TABLE_A WHERE B is not null AND B <> ' '; — polycarp
@polycarp you could not have got the same result, since there cannot be any records that satisfy the where criteria. — Shadow
i am getting Temp table Table_A from excel sheet using vba DoCmd.TransferSpreadsheet, so i think the blank will be null — polycarp
Your SQL syntax works for me but deal with the possibility of either Null or empty string: INSERT INTO TABLE_B(B) SELECT B FROM TABLE_A WHERE B & "" <> "" — June7

ashleedawg ashleedawg · Accepted Answer · 2018-02-10T10:38:59

Specify criteria that excludes both Null values and Zero-Length Strings:

INSERT INTO TABLE_B(B) SELECT B FROM TABLE_A WHERE B Is Not Null AND B <>'';

More info :

NULL v. Zero Length String

Read table without blank rows to Insert to other table using vba

1 Answers

More info :