Receiving <> in crosstab query

[Brian62]

I created a query to pull information from my table. The query is a crosstab and I am getting <> in a cell that has no information. I know what the problem is but can't figure it out.

Here is what I have in each of the crosstab:

(Year([DateofAudit])*4+DatePart("q",[DateofAudit])-1)
Group By

Qtr: (Format([DateofAudit],"q"" Qtr - ""yyyy"))
Row Heading

Letter
Table: QryConsentsforGraph
Group By
Column Heading

SumOfTotal: FindingTotal
Table: QryConsentsforGraph
Sum
Value

I think there is a problem with letter and SumOfTotal: FindingTotal, but I don't know how to show just the Letter which is just the Alphabet I input to correspond with an audit finding. Even if I put the letters A-H with a number value it (<>) will still show zero's just before the Alphabet.

Example:

Qtr ---------<> --A---B---C
1 Qtr - 2011 -0----0---2---2
2 Qtr - 2011 -0----2---2---2

 

[Banana]

<> is created whenever you build a crosstab that may have data that do not match anything listed in the PIVOT clauses. You'd need to look at your underlying queries and verify that:

1) you're not using left outer join where it's not needed
2) you're not including excessive rows that otherwise shouldn't be shown in the final output.

 

[Brian62]

Neither is the case. I am only using one table so there is no outer join and I am only using a few rows that should not cause this problem. Puzzled.

This is the SQL view.

TRANSFORM Sum(QryConsentsforGraph.FindingTotal) AS SumOfTotal
SELECT (Format([DateofAudit],"q"" Qtr - ""yyyy")) AS Qtr
FROM QryConsentsforGraph
GROUP BY (Year([DateofAudit])*4+DatePart("q",[DateofAudit])-1), (Format([DateofAudit],"q"" Qtr - ""yyyy"))
PIVOT QryConsentsforGraph.Letter;

 

[boblarson]

I believe Nulls might also affect this.

 

[Brian62]

It did not like nulls in the Letter column. Thanks!

 

[boblarson]

Actually I just confirmed this. Nulls for a row will cause that to appear.

 

[Galaxiom]

Interesting, I didn't realise that literals could be done like that and expected the Q to be interpreted.

Format([DateofAudit],"q"" Qtr - ""yyyy"))

I would have used the following alternative syntax without the separate quoted sections:

Format([DateofAudit],"q \Qtr - yyyy")

Note that because t and r have no meaning to the Format function (in English) they are automatically interpreted literally. However since q does have a meaning it is escaped with a backslash.

I said in English because we discoved recently the Date format strings use the regional settings when a Greek poster was getting strange results using "m" with DatePart.

 

[Brian62]

All suggestions worked well. Thanks again!