NZ function in Crosstab Query 2

[Melagan]

Greetings,

Probably a simple one here, but the answer is escaping me at the moment. I'm just trying to use the NZ function to fill in null values with zeros on my xtab query.

The only thing it seems to be doing, however, is losing the currency format in all of the fields.

TRANSFORM nz(Sum(qr.EOTotal)) AS [The Value]
SELECT qr.[Sales Rep], qr.rMonth
FROM [qryRep-EO] AS qr
GROUP BY qr.[Sales Rep], qr.rMonth
PIVOT qr.First_Last;

The EOTotal field is currency formatted. For some reason, the NZ function is converting those cells to strings, thereby replacing any EOTotal cells that are null with a zero length string instead of a zero currency value. What gives?

TRANSFORM nz(Sum(qr.EOTotal),0) AS [The Value]

..yields the same result. Any help is greatly appreciated, and please pardon my newness with crosstab queries =)



~Melagan
______
"It's never too late to become what you might have been.

 

[PHV]

And this ?
TRANSFORM CCur(Nz(Sum(qr.EOTotal),0)) AS [The Value]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Melagan]

hehe...yeah that works =) Thanks again PHV! Have a star for the lightning-fast response.


~Melagan
______
"It's never too late to become what you might have been.