Don't show null columns

[jenniferspnc]

I have a query that pulls in records based on criteria a user inputs into a
form that looks up data in another table. Basically they type a list of
countries and it pulls in support details. In the query results I've listed
all the columns; however, I'm going to be creating a report and many times
not all the columns have data listed. How can I adjust the query so that it
only pulls in records and those columns with data?

For example,
Country America APAC EMEA
ABC x
XZY x
123 x

So the results indicate a result in the columns labeled America and APAC;
however, this time EMEA doesn't have anything therefore I wouldn't want to
display that column. Thanks.

 

[Golfinray]

In the criteria of the column put Is not Null

 

[jenniferspnc]

If I put that in all the columns then it won't return any records and it
still displays all the column headers. My request may have not been that
clear...I don't want the column(s) to show if there isn't any data in them.

Another result that could return
Country America APAC EMEA
789 x

So how would I get it so that it doesn't show the column "America" and "EMEA"

 

[Jeff Boyce]

Maybe not a "query" question.

A query will return all the rows (and values, and nulls if none).

But a report might allow you to better control how the output was displayed.

JOPO (just one person's opinion)

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Golfinray]

I don't know of a way to not show query column names unless it is
programatically. I think you can do it in sql but is quite difficult. Try
doing it in a form.

 

[John Spencer]

Here is a function that you can paste into a module that will build the SQL
statement if it is passed a table name (or a query name)

One you have the SQL statement, you can assign it to a query.

Public Function fBuildNoNullColumnSQL(strTableName As String)
Dim strSQL As String
Dim rstAny As DAO.Recordset
Dim dbAny As DAO.Database
Dim tDefAny As TableDef
Dim iLoop As Long

Set dbAny = CurrentDb()
Set tDefAny = dbAny.TableDefs(strTableName)

For iLoop = 0 To tDefAny.Fields.Count - 1
strSQL = strSQL & ", Count([" & tDefAny.Fields(iLoop).Name & "]) as F"
& iLoop
Next iLoop

strSQL = "SELECT " & Mid(strSQL, 2) & " FROM [" & strTableName & "]"
Set rstAny = dbAny.OpenRecordset(strSQL)

strSQL = vbNullString
For iLoop = 0 To tDefAny.Fields.Count - 1
If rstAny.Fields(iLoop) <> 0 Then
strSQL = strSQL & ", [" & tDefAny.Fields(iLoop).Name & "]"
End If
Next iLoop

If Len(strSQL) > 0 Then
strSQL = "SELECT " & Mid(strSQL, 2) & " FROM [" & strTableName & "]"
Else
MsgBox "No fields have a value"
End If

fBuildNoNullColumnSQL = strSQL

'Test - Build a new query and open it. This errors if a query named
"TESTING" exists
'which it will the next time you run this code unless you delete the query
after using it.
Dim qdf As QueryDef
Set qdf = dbAny.CreateQueryDef("TESTING", strSQL)
DoCmd.OpenQuery "Testing"

End Function




--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..