Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2006
    Posts
    157

    Question Unanswered: Computed Value on Form, Displays Error on Report

    hi everyone,

    just hoping someone could make this clear to me.

    The value i display on one text box is an average of a field in the query behind a form.

    With a report that displays the same query, the textbox displays #Error


    Form1 recordsource Table1 which has Field1
    Textbox1 controlsource = Avg([Field1])
    this displays the average correctly
    however

    Form1 recordsource Table1 which has Field1
    Textbox1 controlsource = Avg([Field1])
    this one displays #Error


    ...additionally, Field1 displays properly in both cases

    am I missing something here?


    thanks,

    se
    Only quitters quit!

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Where on your report is Avg(Field1) displayed?
    If it's not in the footers / group section then stick it in there and try again
    George
    Home | Blog

  3. #3
    Join Date
    Apr 2006
    Posts
    157
    ...after a few failed attempts

    i just realized that the Avg function cannot "average" the values of elements on a report...(that is, if I have a textbox named "Joe" bound to a field named "Jane", if I set a textbox at the footer to have a value = Avg([Jane])
    an error would occur)

    kinda sounds funky but... there it is

    anyway, i've had to setup a query object to facilitate my task, so that the query's filter would depend on the user determined values on the calling form...

    ReportQuery
    Field1 = [Forms]![CallingForm][ComboValue]

    Report
    TextBoxAvg
    .Value = Davg("Field1","ReportQuery")


    It's probably the standard approach anyway, I just happened to be this lawbreaker that gets irritated everytime I can't get expected results from access...

    thanks again,

    se
    Only quitters quit!

  4. #4
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    I think the problem you are running into is a naming issue on the report. When you add fields from the Field List to your report, the name of the textbox control is the name of the field (ex. Field1). Then when you make the field calculated using Sum or in your case, Avg, the source becomes =Avg(Field1). When Access tries to calculate the average it does not know if you are talking about the Field1 field or the Field1 textbox. I will rename the textbox to something like txtField1. Then the error should go away.

  5. #5
    Join Date
    Apr 2006
    Posts
    157
    for the record,

    i did try out playing with the names, but similarly, error did display

    i think i'm stuck with using davg for reports

    anyway, the reason i so wanted to use the avg function was so that
    the reports resulting averages would dpend on the calling form's filter
    unlike when they just have the same query,
    when a filter is applied on the form, it does not automatically reflect on the report

    but thanks for replying
    se
    Only quitters quit!

Posting Permissions

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