Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2009
    Posts
    12

    Unanswered: Error 3071 due to =Avg([RAW_SCORE]) in Report footers

    This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables. (Error 3071)

    The query runs fine ... but the report gives this error. I have determined it is the average of RAW_SCORE that is causing the problem. The report runs fine if I eliminate the Avg function or if I limit the number or records it is reporting on by separating the departments.

    The query is as follows:

    Code:
    SELECT [CSR_LN]+", "+[CSR_FN] AS [CSR Name], USERS.first_name AS CSR_FN, USERS.last_name AS CSR_LN, [SUPV_LN]+", "+[SUPV_FN] AS SUPERVISOR, USERS_1.first_name AS SUPV_FN, USERS_1.last_name AS SUPV_LN, USERS_2.last_name AS EVALUATOR, EVALSCORE.[YES] AS YES_SCORE, EVALSCORE.[NO] AS NO_SCORE, EVALSCORE.NA AS NA_SCORE, USERS.active_flag AS ACTIVE, IIf([EVALUATOR_ID] Between 692 And 695,2,IIf([EVALUATOR_ID]=755,2,1)) AS EVAL_type, EVALSCORE.department_id, ([YES_SCORE]/([YES_SCORE]+[NO_SCORE])) AS RAW_SCORE, EVALSCORE.evaluation_date
    FROM ((EVALSCORE INNER JOIN USERS AS USERS_2 ON EVALSCORE.evaluator_id = USERS_2.user_id) INNER JOIN USERS ON EVALSCORE.csr_id = USERS.user_id) INNER JOIN USERS AS USERS_1 ON EVALSCORE.team_leader_id = USERS_1.user_id
    WHERE (((EVALSCORE.department_id)=1 Or (EVALSCORE.department_id)=4 Or (EVALSCORE.department_id)=6 Or (EVALSCORE.department_id)=10) AND ((EVALSCORE.evaluation_date) Between [Start Date?] And [End Date?]))
    ORDER BY EVALSCORE.evaluation_date;
    Screenshot of the report is attached (can't figure out how to attach the report without attaching the entire database). If you need further information, I will gladly provide it, just tell me what you need!!

    Thank you in advance for your assistance, as our IT department does not support Access reporting.
    Attached Thumbnails Attached Thumbnails quality.bmp  

  2. #2
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580
    Try this perhaps: Check for null values on within your data:

    =AVG(RAW_SCORE) might be trying to calculate the average of a subset which includes erroneous or null data;

    RAW_SCORE is calculated from other fields:
    ([YES_SCORE]/([YES_SCORE]+[NO_SCORE])) AS RAW_SCORE

    So check that none of these underlying fields are null.
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  3. #3
    Join Date
    Feb 2009
    Posts
    12

    Error 3071 due to =Avg([RAW_SCORE]) in Report footers

    Some of this data will be "0". For instance if all criterion are met ... then the no field will be 0 ... or vice versa.

  4. #4
    Join Date
    Feb 2009
    Posts
    12
    I have been trying to work around the whole Average thing ... but it seems no matter how I do the math ... whether counting the # of records ... adding the [Raw_Score] field and dividing ... it results in the error. Is there an average function that will not freak out when there are zeroes?

Posting Permissions

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