Only show column that has a value

Question

I'm trying to show columns in a query where the value is not null in Ms Access (2010).

I have a query that now gives me this, where name, field1,... are the column headers:

Name  | field1 | field2 | field 3 | field 4 | field5
mike  | x      |        | x       | x       |

So now I don't want to see Field2 and Field5 in that query. I have a lot more fields then 5, and I only want to see the one with a value.

It's also fine If you can tell me a way to get the column names ( ex field1, field3,field4)

Thanks

Jens Jens · Accepted Answer · 2014-08-22T10:15:15

You'll have to use a simple WHERE statement in your SQL. For example:

SELECT tbl.*, tbl.field2, tbl.field5
FROM tbl
WHERE tbl.field2 Is Not NULL AND tbl.field5 Is Not Null;

This will return all fields and all rows, where field2 and field5 are not Null.

EDIT: Since I am more familiar with vba, I would use the following approach to reach the desired output as stated in the comments. Start by creating the query.

SELECT tbl.*
FROM tbl
WHERE tbl.ID = 12345;

Let's say you name it "qry_record". Afterwards, I would loop through the fields with a recorset, and create a new dynamic sql statement with the fields that have a value.

Dim rs As Recordset
Dim fld As Field
Dim sqlstatement As String: sqlstatement = "SELECT "
Set rs = CurrentDb.OpenRecordset("qry_record")

With rs
    .MoveFirst
    For Each fld In .Fields
        If IsNull(fld.value) = False Then
            Debug.Print fld.Name
            sqlstatement = sqlstatement & "tbl." & fld.Name & ", "
        End If
    Next
End With
rs.Close
Set rs = Nothing

sqlstatement = sqlstatement & "FROM tbl"
DoCmd.RunSQL sqlstatement

EDIT: I have tested the code, and made a minor adjustment. Should work fine now on any table in your access database.

Only show column that has a value

1 Answers