Urgent: Still having trouble showing zeros:Critical

[e777akerson]

I am trying to get a crosstab to count the number of text values in a field but even if I use the NZ function I cannot get any zeros to show. The Column headings show properly but those without any values are entirely blank.
Where do you insert the NZ function in the criteria line with an IIF function or as an expression in the Field area?

 

[Darrylles]

Hi,

Don't know if this is it, but I got around similar problems by filtering out / formatting results in a query which I then supplied to the cross-tab.

Regards,

Darrylle "Never argue with an idiot, he'll bring you down to his level - then beat you with experience." darrylles@totalise.co.uk

 

[e777akerson]

So you were able to get your zeros to show for those columns that had no values?

 

[KornGeek]

I haven't worked with crosstab queries, but in another query where I was calculating totals of fields, I found that I had to specify the join properties between tables correctly for it to work. If I had it set to "records where both tables are equal" (or something like that), it wouldn't display the zero records.

I don't know if this applies to your problem, but it might give you one more thing to examine.

Good luck.

 

[jmshik]

I am not exactly sure how your query is set up, but with my problem trying to find the sum of precipitation:
SELECT Sum(IIf(IsNull([precip]) Or [precip]=0,0,1)) AS CtRain
(Thank you, Mr. Watson!)
let me know if this helps!

 

[e777akerson]

Sorry all but so far none of the suggestions have worked although it is intersting to see all the variations of the IIF IsNull statements. I will struggle on unless one of the forum experts has an idea where I am going wrong.
Thanks to everybody for their help and suggestions.