How to create dynamic report pending on user parameter

[Mr Key]

Hi, I hope someone could help me on this, I have a query of several fields from which I want to create report of few fields selected by user. Say total fields be 30 as Field1...Field3 0, The first three fields will be displayed on the reports but only one of the rest fields (Field4...30) will be available depending on the user request. The User will be prompted to enter one field only to view the report. This report should be displayed on the same template

[liimra]

Solution/

You can use conditional formatting. It should do it for you. Suppose you have a form called "SeA", and you have a Combo Box called "Cmbo" from which the user will select the fourth field he/she wants. In the fourth field of the Query-Report (DesignView), you can use the following:

 FourthField: IIf([Forms]![SeA]![Cmbo] = "Catg1",[Field1], IIf([Forms]![SeA]![Cmbo] = "Catg2",[Field2],IIf([Forms]![SeA]![Cmbo] = "Catg3",[Field3],IIf([Forms]![SeA]![Cmbo] = "Catg4",[Field4],""))))

The above applies for 4 fields. Use same method for the 27 fields and you are done.

Regards,
Ali

[Mr Key]

Thankx alot Liimra! I have tried to implement your Idea by Creating a query that have three Fields and tried to enter the Expression "ForthField " in the ForthFied of a myquery named "Query1" and Run this query, but it displays only the first entered three fields and the ForthFied is empty, may be because of the following reasons:
1. The terms "Catg1", "Catg2","Catg3" ,"Catg4" is not well understood and not defined in my IIfs expression
2. The Combobox, Selects one records from all fields and by default its Unbound
Due to these reasons:
(a)How am I going to set values to these "Catg1", "Catg2","Catg3" ,"Catg4"?
(b)Should I Bound this Combobox to something as?

[Mr Key]

I have also tried to inlude CheckBox to decide on the column it couldn't work

[liimra]

Solution//

You are welcome. I wanted to test the result using all 27 fields. Unfortunately, when using the IIF statement for 19 times, you will probably get "complex expression" error. That is why you create two fields in the Query (each will handle part of the 27 fields); Then you concatenate both fields in another field which will be displaying the extra field based on the user's choice.

Kindly note what I just stated in the attached database.

Regards,
Ali

[Mr Key]

Thankx Mr Ali, It sounds like miracle to me, I have been working for it for months now without any solution. This is what I was looking for. Thankx