How format report / subreports

"The data comes from a query."
Can we see this query please? (if possible in a sample file...)

Dynamically removing "columns" is always a bit tricky.

I am just thinking if you can display this info in a different way to avoid all the machinations of removing columns dynamically.

But lets see if another expert has anything more innovative...

;-)

JeffCoachman

Do you mean subreport(s).  Like 34 of them?  I suppose I can do that.  But somehow I have to make a subreport not be there if there is no data.

Interesting.  I will surely give this a try.  But I do not know how to revise the query to exclude fields that are null.  Can someone help?  Here is a sampling of the SQL:

SELECT tblMatlSpecsChemical.HeaderID, tblMatlSpecsChemical.Spec_ID, tblMatlSpecsChemical.C_min, tblMatlSpecsChemical.C_max, tblMatlSpecsChemical.C_aim, tblMatlSpecsChemical.Mn_min, tblMatlSpecsChemical.Mn_max, tblMatlSpecsChemical.Mn_aim, tblMatlSpecsChemical.P_min, tblMatlSpecsChemical.P_max, tblMatlSpecsChemical.P_aim, tblMatlSpecsChemical.S_min, tblMatlSpecsChemical.S_max, tblMatlSpecsChemical.S_aim
FROM tblMatlSpecsChemical
WHERE (((tblMatlSpecsChemical.HeaderID)=[Forms]![frmMatlSpecsHeader]![txtHeaderID]));

Try this:

SELECT HeaderID, Spec_ID, C_min, C_max, C_aim, Mn_min, Mn_max, Mn_aim, P_min, P_max, P_aim, S_min, S_max, S_aim
FROM tblMatlSpecsChemical
WHERE HeaderID=[Forms]![frmMatlSpecsHeader]![txtHeaderID] AND (C_min IS NOT NULL OR C_max IS NOT NULL OR C_aim IS NOT NULL)

Select allOpen in new window

I only added 3 fields in this criteria because of your sample but if you intend to show all the other fields then revise your criteria accordingly.

Here's a variation on the criteria:

WHERE HeaderID=[Forms]![frmMatlSpecsHeader]![txtHeaderID] AND NOT (C_min IS NULL AND C_max IS NULL AND C_aim IS NULL)

Select allOpen in new window

Ron

I have this SQL now after added another 3 fields:

SELECT tblMatlSpecsChemical.HeaderID, tblMatlSpecsChemical.Spec_ID, tblMatlSpecsChemical.C_min, tblMatlSpecsChemical.C_max, tblMatlSpecsChemical.C_aim, tblMatlSpecsChemical.Mn_min, tblMatlSpecsChemical.Mn_max, tblMatlSpecsChemical.Mn_aim
FROM tblMatlSpecsChemical
WHERE (((tblMatlSpecsChemical.[HeaderID])=[Forms]![frmMatlSpecsHeader]![txtHeaderID]) AND ((tblMatlSpecsChemical.[C_min]) Is Not Null)) OR (((tblMatlSpecsChemical.[HeaderID])=[Forms]![frmMatlSpecsHeader]![txtHeaderID]) AND ((tblMatlSpecsChemical.[C_max]) Is Not Null)) OR (((tblMatlSpecsChemical.[HeaderID])=[Forms]![frmMatlSpecsHeader]![txtHeaderID]) AND ((tblMatlSpecsChemical.[C_aim]) Is Not Null)) OR (((tblMatlSpecsChemical.[HeaderID])=[Forms]![frmMatlSpecsHeader]![txtHeaderID]) AND ((tblMatlSpecsChemical.[Mn_min]) Is Not Null)) OR (((tblMatlSpecsChemical.[HeaderID])=[Forms]![frmMatlSpecsHeader]![txtHeaderID]) AND ((tblMatlSpecsChemical.[Mn_max]) Is Not Null)) OR (((tblMatlSpecsChemical.[HeaderID])=[Forms]![frmMatlSpecsHeader]![txtHeaderID]) AND ((tblMatlSpecsChemical.[Mn_aim]) Is Not Null));

But when I run the query I still see the empty fields and don't want to.  (Mn fields are empty in the underlying table)

Here is what I'm getting (see attached)

(I don't want to see the "Mn" fields)

But I do not know how to revise the query to exclude fields that are null

 A query has a fixed set of columns.  If you want to eliminate some, that is a different query.  You can't eliminate columns on the fly.  Your query can be built to select or ignore ROWS but columns are always fixed for a particular query.

In any event, queries are not meant to be viewed.  They are meant to be used as the RecordSource for forms/reports or as recordsets that you process in VBA.  In a form or report, you have the ability to hide/show controls and that might solve your problem.

(I don't want to see the "Mn" fields)

Your report is where you would view the results of your query so I'm not following what you mean by this.  In the sample image you had, there were 3 cells under each column.  Do those refer to C_min, C_max, and C_aim?  Do you intend to have more than 3 rows for each group or just these specific three?

Steve,

If your question here is :
"Can this be done easily?"
...then the answer is no.

As you know, ...given enough time and energy, ...anything can be done...
;-)

Pat Stated:
"Your query can be built to select or ignore ROWS but columns are always fixed for a particular query."

...the same can be said for a report.
A report hardcodes the fields, (Based on the recordsource)

In any event, you can simulate hidden columns in a report by shrinking the control's width, then moving the remaining controls over to fill the empty space.
(see the attached sample file)
But the code to do this is complex, ...and would have to be even more complex to include your "wrapping" requirement.

Lets see if the other experts can help further...

JeffCoachman
Access--EEQ27327999HideFieldsColumnsInAR

I'm a bit confused with what you want to show in your report. Can you identify the fields here?