crosstab invalid use of null

[scottian]

ive got a crosstab query to count the number of completed jobs grouped on age. I was getting an error invalid use of null. So did a bit digging around found the Nz function but im still getting the error. Does anyone have any ideas?


TRANSFORM Count(Nz([FBR])=0) AS [The Value]
SELECT qryNR.Fuel, Count(qryNR.FBR) AS Total
FROM qryNR
GROUP BY qryNR.Fuel
PIVOT qryNR.Age_Band;


"Children are smarter than any of us. Know how I know that? I don't know one child with a full time job and children."...Bill Hicks

 

[JerryKlmns]

Nz([FBR])=0

Should be

Nz([FBR], 0)

Or

Nz([FBR])

 

[dhookom]

Null values in the Count() would not cause the error message. Is it possible that Age_Band might be null? If so, try:

TRANSFORM Count([FBR]) AS [The Value]
SELECT qryNR.Fuel, Count(qryNR.FBR) AS Total
FROM qryNR
GROUP BY qryNR.Fuel
PIVOT Nz(qryNR.Age_Band,"No Age Band");

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]