Select rows with null values

[jabarlee]

Hello everyone,

the title is not very accurate, I need your help with this:
How can I select (actually delete) a record where all the values except the first column are null? For example:

vvvv
xxxx	1234
yyyy			werwe
zzzz

I would like to select records 1 and 4.
The first column will always have a value, and the number of columns is not fixed.

[pbaldy]

The variable number of fields is suspect. The only way that comes to mind would involve VBA. Simplest offhand is opening a recordset on the table and looping the fields collection, skipping the first. If every field is null delete the record, else move to the next.

[Micron]

Wouldn't "DELETE FROM yourTable WHERE fld2 Is Null AND fld2 Is Null AND fld3 Is Null AND ..." work?
Or am I missing something (likely)?

EDIT
actually more like DELETE yourTable.* FROM yourTable WHERE ((yourTable .fld2) Is Null) AND ((yourTable .fld3) Is Null) ((yourTable .fld4) Is Null)...
I was in too much of a rush I guess.

[pbaldy]

You missed "the number of columns is not fixed". That would certainly work with a static table.

[Micron]

My bad. I should have re-read because it just seemed too simple. Agree with your answer.

jabarlee: if you go that route and need help, you will have to at least post the name of the query that's involved although a zipped copy of your db would be even better to work with.
If you only have a couple of dozen records, you might as well manually delete them. You can select contiguous records in a table if that helps. Not sure what happens in Access if you filter a table/query to where all said fields are Null, select all rows then delete. I'm pretty sure you can do this in Excel and only the visible rows are deleted.

However, you might be dealing with a crosstab query if the number of fields isn't constant. In that case, I doubt you will be able to do this even via vba. Can you explain why the field count is variable?

[jabarlee]

Thank you for your answers.

this is a temp table derived from a crosstab query, hence the column count is not fixed. (It is a table with lab exam results, per date).
As the crosstab query results in many records that actualy have no lab results entries, I want to delete them to clean it up for presentation purposes.

To loop through a record set seems a pretty clever idea, I will try it and come back with the results.

I wonder though, how come that SQL doesn't have a "SELECT * EXCEPT"-like function, that would be really useful

[pbaldy]

It would be handy, but to my knowledge doesn't exist.

[Micron]

A ct query is for arranging normalized data from rows to columns. You shouldn't be making tables that are structured that way.

Seems to me that there is a sort of "except" thing. It's called criteria, no?

[CJ_London]

the problem is not knowing the number of fields. only way I think you could do it is with a vba function looping through a recordset. Something like this untested code

Sub delEmpties(Tname As String)
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim i As Integer
    
    Set db = CurrentDb
    Set rst = db.OpenRecordset("SELECT * FROM " & Tname, dbOpenSnapshot)

    While Not rst.EOF

        For i = 1 To rst.Fields.Count - 1

            If Nz(rst.Fields(i), "") <> "" Then Exit For 'i count will be less that field count

        Next I

        If i = rst.Fields.Count Then db.Execute "DELETE * FROM " & Tname & " WHERE " & rst.Fields(0).Name & "= '" & rst.Fields(0) & "'" 'assumes the first field is text

        rst.MoveNext

    Wend

    set rst=nothing
    set db=nothing

End Sub

[pbaldy]

There's your fish.

[Micron]

Never mind - I obviously was/am having a mental breakdown.

[CJ_London]

I don't normally provide the fish, just the rod. But it's a slow evening - wife is watching her favourite programme and I'm looking for distractions to avoid doing the paperwork

[jabarlee]

Ajax thank you, I didn't see your answer in time!

I used the following code and it works, do you think it is alright?

    Dim SQL As String
    Dim rs1 As DAO.Recordset
    Dim fld As DAO.Field
    Dim f As Integer

    SQL = " SELECT * FROM tbl_TEMP_LabResults_Full"
' I used a dynaset recordset so I can use the .delete method
    Set rs1 = CurrentDb.OpenRecordset(SQL, dbOpenDynaset)
    
    If Not rs1.BOF And Not rs1.EOF Then
        rs1.MoveLast
        rs1.MoveFirst
        While (Not rs1.EOF)
            f = 0
            For Each fld In rs1.Fields
                If Len(fld.Value) > 0 Then f = f + 1
' the 1st column has every field filled, so if there is at least a second field that is not empty, exit the loop
                If f = 2 Then Exit For
            Next
'if there is only one (the 1st) field filled, delete the record
            If f = 1 Then rs1.Delete
            rs1.MoveNext
        Wend
    End If
    
    rs1.Close
    Set rs1 = Nothing

[pbaldy]

Good job and thanks for proving that people don't always need the fish handed to them. In my experience you don't need:

If Not rs1.BOF And Not rs1.EOF Then
  rs1.MoveLast
  rs1.MoveFirst
...
End If

The EOF loop will start at the first record anyway, and won't error on an empty recordset. Is the test accounting for Null values? I'd typically use

If Len(fld.Value & vbNullString) > 0 Then

I'm not on a computer so can't test yours.

[jabarlee]

Paul, thank you for your answer.

You're right, I removed those lines and everything is ok.
As for the Null values, LEN(fld.Value) > 0 does work, but now I realize that it might as well not have worked. I have used it several times, though