UtterAccess Discussion Forums > Access Grouping Based on Values??

Full Version: Access Grouping Based on Values??

UtterAccess Discussion Forums > Microsoft® Access > Access Reports

smurray

May 1 2007, 09:38 AM

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:

CODE

Fund Name,  Fund Number    Requested Amount
General Fund,    001
  Circuit Court                         50,000      ***need the 001 fund to list each department and a grand tot.
  Prosecutor                            55,000
Total                                       105,000

Family Counseling Fund ,  526           35,000     ***not broken down by department, just grand totals
Alt. Dispute Resolution Fund, 545     15,000
Motor Vehicle Highway Fund,  101     500

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:

CODE

If txtNumber.Value = "001" And txtDepartment.Value <> "" Then
txtDepartment.Visible = True
End If

ScottGem

May 1 2007, 10:20 AM

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.

smurray

May 1 2007, 01:46 PM

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

Shannon

ScottGem

May 1 2007, 02:16 PM

Glad to assist

smurray

May 2 2007, 12:23 PM

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) :

CODE

Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)
If txtNumber.Value = "001" Then
Detail.Visible = True And GroupFooter1.Visible = True
ElseIf txtNumber.Value <> "001" Then
Detail.Visible = False
End If

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:

CODE

Any ideas on this?

Thanks,
Shannon

ScottGem

May 2 2007, 12:56 PM

Try it this way:

CODE

Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)

If txtNumber = "001" Then
    Detail.Visible = True
    GroupFooter1.Visible = True
    txtHeaderAmount.Visible = False
Else
    Detail.Visible = False
    GroupFooter1.Visible = False
    txtHeaderAmount.Visible = True
End If

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.

smurray

May 3 2007, 08:56 AM

That worked perfectly!! Thanks again!

Shannon

This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.