Nz? Syntax (1 Viewer)

[teiben]

I thought I've done this before, searched Nz w/no luck, any who, i have a cross tab query which works correctly, but I want it to return 0 instead of blanks, this is my syntax in a query:

expr1: idcardiotype
count
value

 

[vbaInet]

What did you try that didn't work teiben?

Also, there could be more to this. Have you got any criteria set?

Does it display all results in a SELECT query without the Count aggregate?

 

[teiben]

[idcardiotype]+Nz([idcardiotype]) and Expr1: Count(Nz([idcardiotype],0))

 

[vbaInet]

And what about these questions?

Also, there could be more to this. Have you got any criteria set?

Does it display all results in a SELECT query without the Count aggregate?

 

[teiben]

no there is no select query, just a crosstab, so there's:

Row Heading a Column Heading and the expression which is count, which works fine on the idcardiotype but i can't seem to use the nz command correctly. Are you saying it doesnt work in crosstabs?

 

[vbaInet]

Alright, let's see the SQL statement of the crosstab query. Remember to enclose it in Code tags.

 

[teiben]

TRANSFORM Count(Nz([idcardiotype],0)) AS Expr1
SELECT Query1.idcardiotype
FROM Query1
GROUP BY Query1.idcardiotype, [idcardiotype]+Nz([idcardiotype])
PIVOT Query1.cardiotype;

 

[teiben]

Also tried SELECT tbltypesCardio.idcardiotype, tbltypesCardio.cardiotype, tbltypesCardio.idcardiotype
FROM tbltypesCardio
GROUP BY tbltypesCardio.cardiotype, tbltypesCardio.idcardiotype, tbltypesCardio.idcardiotype;

 

[vbaInet]

What you do is apply the Nz() function in your Query1 query instead of in the Crosstab query.