I am designing a report and need it to group based on a value in the query that the report is based on. My report has five controls- "Fund Number", "Fund Name", "Department", "Requested Amount", and then a control that sums the requested amount grouped by fund. Basically, what I would like the report to do is if "Fund Number" is equal to 001 then I need the Departments to be listed under that fund (along with each department's requested amount and a total)...however, for all of the other funds, I don't want them to list the department...just the total amount requested for each fund. Can this be done? I would really appreciate any help or suggestions on this. Here's an example of what I would like this to look like:

I've tried some code in Visual Basic for the report Activate event, but I can't get it to return just the department & requested amount for only fund 001. What it does is if fund 001 exists at all in the results then it displays departments for all of the funds. Here's what I tried:

What you could do is use put a control for the total in the Group (Fund) Header. In the Onformat event of the header test for the 001 fund and its it not that fund then set the Visible property of the total to yes and the Detail and Footer to No. Otherwise reverse it.

I finally got it to work based on your suggestion...thanks for your help!!

Shannon

Glad to assist

I have run into an issue with my code and was wondering if anyone could help me with it. I have it working where it shows the sum control in the footer for all funds...I would like to show the footer sum control for just fund 001 and the header sum control for the rest of the funds. I have two sum controls that both sum Requested Amount...one is in the header (txtHeaderAmount) and one is in the footer (txtFooterAmount). I set the txtHeaderAmount's default visible property to no...txtFooterAmount is at yes. When I use the following code, it gives me the footer total for all funds. Here is my code (it is in the OnFormat event for the fund header as suggested by ScottGem) :


Now, if I add to change GroupFooter1's visible property to False and txtHeaderAmount to true in the second portion of the IF statement (which should give me what I want), it returns the same thing as the one above:


Any ideas on this?

Thanks,
Shannon

Try it this way:



End Sub

The AND operator is for comparisons, not operations. Each assignment of a value should stand alone. You don't need the ElseIf since if the txtNumber is not 001 you want to perform the Else.

That worked perfectly!! Thanks again!

Shannon