User defined columns report

[Deutz]

Hi and thanks in advance,

I am using Access 2010

I have a single table that stores product information and I would like to build a report that gives the user the option to select the fields they want to see in the report. The db is shared so I can't open the report in design mode in code and show/hide textboxes/labels and move textboxes/labels to close up gaps etc. Wondering if there is a way to do it using the sql and the report's event procedures?

Thanks
Deutz

[June7]

Short answer - no. Field names cannot be dynamic in query. Either it's there or it isn't. Could use lots of VBA code to change a QueryDef to assign alias field names:

SELECT fieldname1 AS Field1, fieldname5 AS Field2, ... FROM tablename;

Then the report can have textboxes bound to Field1, Field2, etc. But what about data formatting, length, etc. Still need a static number of textboxes on the report. What if the report allows for 10 fields and user really wants 11?

Suggest you design a standardized set of reports and users pick from a list or click buttons to choose.

[Deutz]

Hi June7,

I kinda thought that might be the case. Will build a set of reports as you suggested.

Thanks
Deutz