Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2005

    Unanswered: Report shows #ERROR for field value..


    I am a relative newbie to MS Access, but I have the following problem. I have a Crosstab Query that is counting the number of values for field "StatusPDCACode" (Typically there are 4 field values for this field P, D,C, and A) and a total. However, when a certain field value hasn't any values (Normally Value A), the Report gives an error:

    The Microsoft Jet Database Engine does not recognize 'A' as a valid field name or expression.

    If I add this statement to the Control Source Property of the A field on the Report - =Nz([A],"") Then I get the report to run, but every rows A field prints #ERROR. I'm trying to get it to show a blank or space in the field. I'd settle for a zero if necessary.

    Below is the Query from the SQL view of the Crosstab Query:

    TRANSFORM Count([Axiom Jobs Database].StatusPDCACode) AS CountOfStatusPDCACode
    SELECT [Axiom Jobs Database].Consultant, Count([Axiom Jobs Database].StatusPDCACode) AS [Total Of StatusPDCACODE]
    FROM [Axiom Jobs Database]
    WHERE ((([Axiom Jobs Database].Comp)=0))
    GROUP BY [Axiom Jobs Database].Consultant, [Axiom Jobs Database].Comp
    PIVOT [Axiom Jobs Database].StatusPDCACode;

    The output should look like the following example:

    Consultant P D C A Total
    Tom Jones 1 2 1 4
    Sam Adams 1 1 1 3
    Tony Andrews 1 1

    However, when there are no A values in the StatusPDCACode field, the above report would have #ERROR in each row's A value.

    Any help would be greatly appreciated. I'm really not sure if the fix is in the SQL statement, or using a function like I tried in the Report?


  2. #2
    Join Date
    Apr 2005
    Zagreb - Croatia
    Try next;
    In the query properties populate "Column Headings" property,
    as "P", "D", "C", "A"

  3. #3
    Join Date
    Dec 2005


    Thanks, that fixed it for me. Seemed like a simple fix, wished I could have figured it out myself.


Posting Permissions

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