Dynamic Fields in a Query

[Psilokan]

My boss asked me to do something today that stumped me. He wanted me to make a query, where when you're selecting the parameters you could chose whether or not you wanted certain fields to be visible.

On top of that I need to be able to chose whether I want it to be shown on screen in a query, printed as a report, or exported to excel. This is what makes it tricky. I could easily do a bunch of If...then statements to generate the SQL query and only show the fields necessary, however the report and the query view both have the fields on them in design view. If I cut the fields out of the query at runtime, then I get the nasty "Input parameter" popup box.

So I've been bashing my head off the keyboard trying to figure out how I'm going to do this. One other theory I tried to put into use was having them left on the form/report, but if the checkboxes were not selected then it would set them to visible = false at runtime. Sounds good in theory, however Access decided to ignore this block of code and just show the fields regardless of what I told it to do.

So if anyone could provide me with some insight or suggestions, I would certainly appreciate it.

Thanks in advance!

 

[SamDeMan]

in the header and body of the report, you create text boxes that are named box1, box2 etc.. you then add to your code a for loop that will look like this:

For intX = intColumnCount To intControlCount
Me.Controls("Head" & intX).Visible = False
Me.Controls("Col" & intX).Visible = False
Next intX

For intX = 1 To intColumnCount
strName = rstReport.Fields(intX - 1).Name
Me.Controls("Head" & intX).Caption = strName
Me.Controls("Col" & intX).ControlSource = strName

basically this is a dynamic report.