How to delete the blank rows?

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.

---

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

---

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.

---

I would recommend a more universal solution:

DELETE tblYourTable.*, tblYourTable.[OneFieldThatIsAlwaysEmpty]
FROM tNewTable
WHERE tNewTable.[OneFieldThatIsAlwaysEmpty] Is Null;

This requires, of course that you have a field in your table that is truely empty (Null)

The advantage is that you do not have to define all the single fields in your table. In the above answers, this is no problem, as the example table has only two or three fields. But usually tables are larger.

Note that a Delete query will not work when your table is not an actual table in Access but a so called "linked table" that takes data e.g. from Excel. In this case you first should tranfer your data into a true Access table.

(This has been tested in Access 2010)