Results 1 to 10 of 10
  1. #1
    Join Date
    Apr 2009
    Posts
    3

    Unanswered: I recieve #error when I create a total in the page footer

    Hello all,

    I would be extremely grateful if someone could provide some assistance with this. Im not sure if its an easy solution or not.

    Basically, I have got a report which, in the detail section are different text boxes called location, date, charge etc.

    These are created via a query. Basically when opening the report the query will ask you to enter two dates, the report will then only show results which are between these two dates.

    Now, I wish to have all of the "charges" totaled up at the bottom of the report.
    If I create a text box with a control source of Sum=([Charge]) the only thing it brings back when the report is opened up is #error

    Interestingly enough, if i enter this sum textbox into the Detail section rather than the page footer it calculates the total fine. However I do not want this as it repeats this total below every single result the query finds.

    I really am not sure where I am going wrong and would be extremely grateful if someone could give some help

  2. #2
    Join Date
    Jul 2004
    Location
    South Dakota
    Posts
    267
    try =Sum(Charge) in the control source instead.

    C

  3. #3
    Join Date
    Apr 2009
    Posts
    3
    Hi Canupus,

    Sum=([Charge]) already is the control source of the text box.

    I can't think for the life of me why this error keeps coming up, Access doesnt say there is a problem with the syntax...


  4. #4
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Is [Charges] a calculated value? You cannot use aggregate functions against a calculated control, you have to use them against the expression that does the calculation.

    For example, if

    [Charges] = [Rate] * [Hours]

    you cannot use

    = Sum([Charges]
    )

    but rather would have to use

    = Sum([Rate] * [Hours])
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  5. #5
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Firstly, it's not Sum=([Charge]) but =Sum([Charge]).

    Is the value you are wanting to put in the page footer supposed to be a total of all the charges on the page or the whole report? If it is for the page, I actually have no idea how you'd do that.

    Not sure, but I don't think you can do =Sum() operations in the page header/footer.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  6. #6
    Join Date
    Apr 2009
    Posts
    3
    Missinglinq - Yes Charge is a calculated value. It is actually Quantity * Price

    I have tried what you suggested but this is also bringing error

    StarTrekker - Sorry Yes is do mean =Sum([Charge])

    The total is indeed supposed to be totaling up everything on the page, And it would seem you are right in saying you cannot do a Sum in the report footer because if i put in the Detail section it works fine. The only trouble is it repeats itself under every single result the query fetches.

    I do not mind if the total is in the footer or the detail section to be honest, I just want it so it is displayed after all of the results to bring a grand total.

    Does anyone have any idea how I would be able to get it to stop repeating in the detail section? Because this would solve the problem.

    Thanks again everyone.

  7. #7
    Join Date
    Apr 2009
    Posts
    67
    This happened to me before I was able to get by having another query that runs the first query and have the second query without the calculation run the report. Good luck

  8. #8
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    You need to put it in the report footer then, not the page footer.

    View - Report Header / Footer if you don't have one.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  9. #9
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    Talking

    Quote Originally Posted by shibtain
    Hello all,

    I would be extremely grateful if someone could provide some assistance with this. Im not sure if its an easy solution or not.

    Basically, I have got a report which, in the detail section are different text boxes called location, date, charge etc.

    These are created via a query. Basically when opening the report the query will ask you to enter two dates, the report will then only show results which are between these two dates.

    Now, I wish to have all of the "charges" totaled up at the bottom of the report.
    If I create a text box with a control source of Sum=([Charge]) the only thing it brings back when the report is opened up is #error

    Interestingly enough, if i enter this sum textbox into the Detail section rather than the page footer it calculates the total fine. However I do not want this as it repeats this total below every single result the query finds.

    I really am not sure where I am going wrong and would be extremely grateful if someone could give some help
    Hi,

    Just place it in the Report Footer and that will work.
    BUD

  10. #10
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Wow, that's very helpful bud, thanks, I wish I had thought of that ^^
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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