do not show query field that does not contain data

[apolite]

I am attempting to create a query in where any field that does not contain
data in relation to the criteria specified will not be shown in the results.
I am aware of the show box however this will not show the field whether it
contains data or not. Please help.

Thank You

 

[John Spencer]

You cannot show or hide a field in different rows of a query. All rows in a
query result will have the same columns. If you want to show or hide entire
columns based on whether or not any record (row) in the result has data in
that column, you could do so by using some VBA to build the query string on
the fly.

You would need to build a query first that counted the number of fields with
a value
SELECT Count(FieldA) as ACount
Count(FieldB) as BCount
FROM YourTable
WHERE <<Your criteria here>>

Then use VBA to report which columns returned zero and build the new query
with a SELECT clause that showed only the columns that had a count other
than zero.

In a report, you can show and hide the controls that show data and you can
programatically move the controls around. The VBA code to do so could get
to be fairly complex. SO you could hide and show specific controls
depending on the values of the controls.

If you were doing a vertical report, you could use the can shrink properties
of the report sections and controls to acheive something like the following

Field A
Field B
Field C
(next Record - next detail section)
Field A
Field C
(next Record - next detail section)
Field D (Wow! First record with a value in FieldD)
(next Record - next detail section)
Field A
Field B
Field C
Field D
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..