creating a report with variable field names 1

[n1nja]

Hi, I am having a bit of a problem trying to create a report as the data source has variable field names. Basically I am running a select query which populates tableA, I then run a crosstab query that looks at the data in tableA, and creates a number of columns dependant on what the first query populated tableA with - this is normally between 1 and 4 different values. If I create the report with all the possible fields present and then run the report, and only 3 of the 4 fields have been created in the table by the query, the report fails. Does anyone know of a way of getting around this? Maybe some kind of condition on the report to check if the field exists before trying to pull it in?

 

[danvlas]

Bind the recordsource of the report to the crosstab query
Create as many invisible unbound text boxes as maximum columns you may have.
In the Open event of the report, open the query as a recordset and find the number of columns returned.
Bind the text boxes to the fields and set their visible property to true.

That should be the idea.

Happy New Year
Daniel Vlas
Systems Consultant
danvlas@yahoo.com

 

[randysmid]

Dan,
Great tip!!! I am giving you a star for that one.
HTH,
Randy Smith
California Teachers Association