<div>
&gt;What would be the best way to delete the rows where all the records are empty<br />
That depends on what 'are empty' means, if that means NULL values or an empty string &quot;&quot;<br />
<br />
&lt;Might want to make a backup before actually executing these&gt;<br />
<br />
For starters.. <br />
DELETE * FROM YourTable WHERE Nz(Column1, &quot;&quot;) = &quot;&quot;<br />
<br />
If you just want to delete all rows and not test if they are empty or not...<br />
DELETE * FROM YourTable
</div>


<div>
so if there are more then one columns i would do something like this?<br />
<br />
DELETE * FROM YourTable WHERE Nz(Column1, &quot;&quot;, Column2, &quot;&quot;, Column3, &quot;&quot;) = &quot;&quot;
</div>


<div>
By empty i mean that they have no value. There is nothing in them
</div>


<div>
to delete records where any field is empty or null aside from the Primary key field,<br />
you only need to use a single field as &nbsp;the criteria<br />
<br />
delete * from tableX<br />
where [column1] &amp;&nbsp;&quot;&quot;=&quot;&quot;<br />
<br />
no need to add the other empty fields, since they are all empty or null
</div>


<div>
Rey,<br />
<br />
They would not ALL be empty. &nbsp;Wouldn't your code delete the rows where column1 is empty even if the other records for that row have a value? &nbsp;I believe i do have to name them as suggested by Jim.
</div>


<div>
I would agree with Jim, probably better to be safe with a WHERE clause that includes all on the non PK fields.<br />
<br />
@vbNetCoder,<br />
<br />
Just so you understand why Jim and Rey are making those particular syntax recommendations . &nbsp;If you know that the fields with &quot;no data&quot; are actually NULL, then you could use:<br />
<br />
DELETE FROM yourTable WHERE (Column1 IS NULL) AND (Column2 IS NULL) AND (Column3 IS NULL)....<br />
<br />
but if those fields could have an empty string, then testing for:<br />
<br />
(Nz(Column1, &quot;&quot;) = &quot;&quot;)<br />
<br />
or<br />
<br />
([Column1] &amp;&nbsp;&quot;&quot; = &quot;&quot;)<br />
<br />
will select records where the field is either NULL, or contains an empty string.<br />
<br />
Dale
</div>


<div>
i do believe they are empty string and not null
</div>


<div>
@vbnetcoder,<br />
<br />
here is your statement above <br />
<br />
<b>What would be the best way to delete the rows where all the records are empty &nbsp;(other then the primary key which will always have a value? </b>
</div>


<div>
Since I don't see any column names and sample data in this question, or logic for what's empty and what's not, and the exact definition of empty, the below code is not intended to be a literal solution but an example that requires you to replace YourTable, Column1, Column2, Column3, with the actual names. <br />
<br />
DELETE * FROM YourTable <br />
WHERE Nz(Column1, &quot;&quot;) = &quot;&quot;&nbsp;AND Nz(Column2, &quot;&quot;) = '&quot;&quot; AND Nz(Column3, &quot;&quot;) = &quot;&quot;<br />
<br />
Since I can't connect to your data source and view the table schema and the data, like <a href="http://www.barney.com" rel="ugc">Barney TBPD</a> I had to use my imagination.
</div>


<div>
<div class="content wysiwyg-content">
I have a table with empty rows. &nbsp;What would be the best way to delete the rows where all the records are empty (other then the primary key which will always have a value?
</div>
</div>


I have a table with empty rows.  What would be the best way to delete the rows where all the records are empty (other then the primary key which will always have a value?

delete query

Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.