Deleting Empty(Null) Records

[Guest]

I have imported some data from Excel into a new Access table. I now have
several records that are null (from empty rows in Excel).

How can i write an SQL DELTE query that will loop through the records in the
table and delete any record where all fields are null? I can code this by
hand, entering each individual field to be parsed, but with 40+ fields,
that's going to take some time.

DELETE tbl_Projects.*
FROM tbl_Projects
WHERE (all fields are null);

Thanks muchly,
/amelia

 

[Nikos Yannacopoulos]

Amelia,

Paste this sub in a general module, then run it:

Sub Delete_Empty_Records()
Dim vTable As String, fld As String
Dim strWhere As String, strSQL As String
vTable = "tbl_Projects"
For i = 0 To CurrentDb.TableDefs(vTable).Fields.Count - 1
fld = CurrentDb.TableDefs(vTable).Fields(i).Name
strWhere = strWhere & "IsNull(" & fld & ") AND "
Next
strWhere = Left(strWhere, Len(strWhere) - 5)
strSQL = "DELETE * FROM " & vTable & " WHERE " & strWhere
Debug.Print strSQL
CurrentDb.Execute strSQL
End Sub

Note: before you try anything, make a backup copy!

HTH,
Nikos

 

[Guest]

ah! thank you kindly!