Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Feb 2002
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query Sum on Report (Access 2000)

    I am trying to place the result of a query sum on a report. It will be displayed in a text box in the report's footer. I have the Control Source for the text box set as the query field which contains the summed result I want. It looks like this: =[qryPledgeSum]![SumOfAmountPaid]

    When I run the report a message box appears asking for the parameter value of the qryPledgeSum.

    Some fields of the table from which I built the query are used in the report body.

    I would have liked to simply sum up the amounts in the report footer using "SUM", but that only worked for the Grand Total Pledged. This [SumOfAmountPaid] is only the amount actually collected so far.

    How can I get the sum result from the query on the report footer?

    How can I be certain it is the most current amount? In other words, does the query have to be run each time the report opens?

    Can I display the sum of only the amount paid on the report? I tried the following SQL statement, but it did not work:
    SELECT Sum(Pledges.AmountPledged) AS SumOfAmountPledged
    FROM Pledges
    GROUP BY Pledges.Paid
    HAVING ((([Pledges]![Paid])=-1));

    Thank you,
    Doug

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Query Sum on Report (Access 2000)

    Try setting the Control Source to :
    = Dlookup("[SumOfAmountPaid]","[qryPledgeSum])

    This will run the query for you and return the current total.

    I am assuming qryPledgesum is a grouping query that returns just a single total.
    Regards
    John



  3. #3
    Star Lounger
    Join Date
    Feb 2002
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Sum on Report (Access 2000)

    Thanks for the reply.
    I still get the message box asking for a Parameter for the qryPledgeSum. I've checked spelling and so forth and still can't find the source of the error.
    Does this query have to be included in the reports record source?
    I only need the one summed result from the qryPledgeSum.

    Doug

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query Sum on Report (Access 2000)

    No, you don't need to include qryPledgeSum in the record source of the report for this. The domain functions DLookup, DSum, DCount etc. retrieve data directly from a table or query. John forgot the closing quotes in his expression:

    = DLookup("[SumOfAmountPaid]","[qryPledgeSum]")

    SumOfAmountPaid should be the exact name of the field that returns the sum, and qryPledgeSum should be the exact name of the query that contains this field.

  5. #5
    Star Lounger
    Join Date
    Feb 2002
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Sum on Report (Access 2000)

    That works fine.
    Thank you, John and Hans.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •