How to delete the blank rows?

Question

Using Access 2003

Table1

ID Name  Value

001 Raja 100
002 Ramu 200


004 Raqu 500



008 Ravi 250

...

Here I want to delete the blank rows in my table?

Delete from table1 ...?

Expected Output

ID Name Value

001 Raja 100
002 Ramu 200
004 Raqu 500
008 Ravi 250

...,

Need Query Help.

Answer 1

Delete from table1 
where ID=' '
and Name=' '
and Value=' '

or

Delete from table1 
where ID is null
and Name is null
and Value is null

Answer 2

If your fields alow zero-length strings and can have white space in them, here's one solution:

  DELETE FROM table1 
  WHERE Len(Trim(ID & ''))=0 
  AND Len(Trim(Name & ''))=0 
  AND Len(Trim(Value & ''))=0;

I'm assuming you're writing your SQL to execute in Access itself, as Trim() and Len() are Access/VBA functions (both are provided by the Access expression service using VBA, so this wouldn't work via ODBC or OLEDB).

Now, that ignores other kinds of issues in those fields, such as tabs or Cr/Lf characters. f the above doesn't delete all the rows, then you'd have to poke around in the fields to see what the actual characters are and then clean them out.

I also hope that the names of your fields are fake, as both NAME and VALUE are Access reserved words and should be avoided in object names at any level in Access/Jet/ACE.