Delete Query Help

[MTSPEER]

Hey Guys,

I have a lot of fields in my table(Table1) and I'm trying to make a query to delete all the records where the values are Null. But there is only 1 field where there has to be a value in it and that is Field1. I tried to build a query with every field in the table(except Field1) stating to delete if the criteria is Null. and I got an error saying the query is too complex. I'm guessing there are too many fields. But I need to have all those fields together stating if they are null or not to delete them. Idk what to do.

-Thanks

[June7]

How many fields? There is a limit of 99 AND operators in WHERE clause. I don't know what the limit for OR operators is, if there is one. Post attempted sql statement.

Exactly what is the criteria? Delete all records where all fields are Null? Why would there be any records where all fields are null?

or

Delete all records where any field is null except Field1? Essentially all records where Field1 is null regardless of what is in other fields?

[JoeM]

So what exactly is the criteria for determining which records to delete? Are you only looking at the one field, or all the fields?
If you are only looking at one field, it will be pretty straightforward:

DELETE [TableName].*
FROM [TableName]
WHERE [TableName].[Field1] Is Null;

[MTSPEER]

Field1 will always be populated. And Im trying to delete the records where all the fields except Field1 are null. Because there will be some records where other fields will be populated.

[June7]

Did you see post 2?

Might require a VBA procedure.

[JoeM]

Exactly how many fields are we talking about? It sounds like you have too many fields to check each one individually. Note that if it only involves one table, you don't need to preface each field name in the query with the table reference. That would cut down on the length of SQL code.

The only other way I can think of is to come up some VBA or User Defined Function that counts the number of Nulls values across all fields for each record. Might have to use some RecordSets in that VBA code.

[MTSPEER]

What would the VBA procedure be?

[MTSPEER]

There's about 130 fields. What do you mean I dont need to preface each field name? Can you give me an example?

[JoeM]

What do you mean I dont need to preface each field name? Can you give me an example?

Compare the WHERE clause here:

DELETE [TableName].*
FROM [TableName]
WHERE [TableName].[Field1] Is Null;

with this one here:

DELETE [TableName].*
FROM [TableName]
WHERE [Field1] Is Null;

But if you have 130 fields, you would need 129 AND clauses in that WHERE clause, and June said:

There is a limit of 99 AND operators in WHERE clause.

so that is really just academic, at this point.

[MTSPEER]

Ah okay. So there's nothing really I can do?

[JoeM]

Ah okay. So there's nothing really I can do?

No, something can probably be done. But some VBA code would need to be developed. I would probably have to play around with it for a while to find something that works.

[MTSPEER]

Is there a way to split the query into 2 of them and somehow combine them together to work as 1?

[June7]

130 fields is a lot.

Might be a way with splitting fields into multiple query objects.

Query1
SELECT ID FROM table WHERE Field2 Is Null AND Field3 Is Null ...;

Query2
SELECT ID FROM table WHERE Field61 Is Null AND Field62 Is Null ...;

Query 3
DELETE FROM table WHERE ID IN (SELECT ID FROM query1) AND ID IN (SELECT ID FROM query2);

However, VBA something like:

Dim rs As DAO.Recordset, intNullCount As Integer, x As Integer
Set rs = CurrentDb.OpenRecordset ("SELECT * FROM tablename;")
While Not rs.EOF
   intNullCount = 0
   For x = 2 to 129
      If IsNull(rs.Fields(x)) Then intNullCount = intNullCount + 1
   Next
   If intNullCount = 128 Then
      CurrentDb.Execute "DELETE FROM table WHERE ID=" & rs!ID
   End If
   rs.MoveNext
Wend

That presumes the table has an ID field and that ID and Field1 are first 2 fields in table design. So the code skips them by not including field indexes 0 and 1 in the loop.

Review https://support.office.com/en-us/art...9-EA9DFF1FA854
Note allowed number of characters in query design grid cell is 1024.

[JoeM]

My other thought is to maybe Concatenate all those fields into a single (or few) calculated fields, and check the lengths of those few calculated fields.