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?