N o Zero for my SUBs 1

[teqtaq]

Hello again.

I am trying to get totals for my numerous SUB reports.
I wrote a Crosstab query that shows blanks where I need to insert 0 (Zero).

Dataype is Numeric. I had tried
IIf(Nz([Field1]),[Field1],0)
IIf(IsNull([Field1]),0,[Field1])
IIf(([Field1])="",0,[Field1])
and many variances of these functions...

Nothing gets me a Zero. Still blank. I have wasted a day already, please, help.

 

[dhookom]

I believe the best solution is to modify your crosstab to return zeros rather than nulls. If you had provided the SQL view of your crosstab, someone would have provided the solution.

It might also help if you told us where you want to display the totals? Are these values calculated in the crosstab or do you want the totals in the main report?

Duane
Hook'D on Access
MS Access MVP

 

[PWise]

how about

nz(Field1,0)

 

[teqtaq]

TRANSFORM Count([TABLE1].eNO) AS CountOfeNO
SELECT [TABLE1].[Office], Count([TABLE1].eNO) AS [Total OfeNO]
FROM [TABLE1]
GROUP BY [TABLE1].[Office]
PIVOT [TABLE1].[Field1];
______________________________

I am getting blanks for totals. I would like to have zeros in the query instead of blanks.

Thanks to all

 

[dhookom]

Try:

TRANSFORM Val(Nz(Count([TABLE1].eNO),0)) AS CountOfeNO
SELECT [TABLE1].[Office], Count([TABLE1].eNO) AS [Total OfeNO]
FROM [TABLE1]
GROUP BY [TABLE1].[Office]
PIVOT [TABLE1].[Field1];

Duane
Hook'D on Access
MS Access MVP

 

[teqtaq]

OMG! It is so working, I can cry...thanks!