Access/ unable to get NZ working in a cross tab 1

[nover11]

I created a crosstab to run days in office. If they take time off it gives a value, if they are in all day it leaves it blank. I would like to return a zero showing that they were in the office that day. I used Sum(Val(Nz([Subject],0))). In the total tab I used expression. Still blanks!

May i have some help please
Thank you
'

 

[dhookom]

Please post your complete SQL view of the crosstab query.

Is the Subject field numeric?


Duane
Hook'D on Access
MS Access MVP

 

[nover11]

Son of a biscuit, i posted the wrong sql. sorry about that

TRANSFORM Sum(Val(Nz([Subject],0))) AS Out
SELECT tblOutOfOffice.Assigned
FROM tblOutOfOffice
GROUP BY tblOutOfOffice.Assigned
PIVOT tblOutOfOffice.Date;

Thank you

 

[dhookom]

Where is the crosstab?

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

What about this ?
TRANSFORM Nz(Sum([Subject]),0) AS Out

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[nover11]

OH SNAP! It worked great, thank you PH!

Thanks again
~Nover11