How do I delete all blank records within a table using a query?

J

julialatte

I have a table that builds from a virtual information exchange system. The
data that comes in isn't always clean. The exact goal of the database I'm
building is to get the data, clean it, and send it out.

How do I write a query to check the table for blank records (or fields, I'll
explain) and delete only the blanks making sure it will even delete a blank
record if it is the last one.

The data that imports goes to a one field table even though the records
coming in are many fields we treat each as a one field record while it is
inside this DB.
 
J

John Spencer

DELETE DistinctRow TableName.FieldName
FROM TableName
WHERE [FieldName] Is Null

If that doesn't work then change the where clause to
WHERE [FieldName] is Null or [FieldName}=""

If that fails then, try
WHERE Trim([FieldName] & "") = ""

If that fails then, you need to look at the blank field a lot closer to see
what is there. Perhaps a lot of spaces and line feed/carriage returns or
some other invisible characters.
 
Top