nz() with crosstabs

indesisiv

Access - What's that?
Local time
Today, 09:09
Joined
Jun 13, 2002
Messages
265
Hi there

I have a crosstab ... but what i need it to do is to display a 0 instead of leaving the fields blank.
I think that i need to use the Nz function but i am not sure as how to use it.

Any help would be extremely grateful.

Steve
 

Attachments

Crosstab Zeros -

Change the Group by row to Expression and put the following as the field:

Expr1: IIf(Count([TableName].[FieldName])>0,Count([TableName].[Field]),0)

The Count function may be another aggregrate function like Sum but the syntax should be the same. Hope it works for you.

GumbyD
 
I think that i need to use the Nz function
Yes, what you need is Nz()+0

In query SQL View, surround the Sum expression with Nz() and add +0:

TRANSFORM Nz(Sum([Contact tracing5_7_1_1: Contact slips_Ctact category].[Count of ContactsID]))+0 AS [SumOfCount of ContactsID]
SELECT ..............
 
Last edited:
Just want to say I really like that solution EMP!

GumbyD
 

Users who are viewing this thread

Back
Top Bottom