Null values again? I want zero number display...Help Please.

[VickyWinters]

I am having a real problem with this null value. I did a crosstab query with female, male, unknown, as column headings. I wizarded a monthly report from the query. Some months contain all females, therefore, leaving blanks in my report for males. How do I get the blank cells to display zero numbers so that I can subtotal and total the amounts?

I have tried the property OnNoData as:
msgbox ("0&quot
cancel = true
This does nothing to my report. Still blank cells.

I have tried the control source as:
=Nz([Male], 0)
and
=Nz([Male], "0&quot
and
=IIf(IsNull([Male]), "0", [Male])

These either give me an error or does nothing to my cells. At times these functions don't even adhere to the box, unless I type the formula directly into the text box.

I don't know much about SQL so I did not try anything in that area. If I could get the query to display zeros that should solve my problem with the reports.

I tried looking through the forum for similar problems. The solution appears as the control source above, but it does not work for me. I also have Counts and Sums presently in some of the control sources already. How would I code those with the formula above? Example: =Sum([Females]

Obviously, I am doing something wrong. Can you help? Please be descriptive. I, like most, on the forum, am learning.

Thanks in advance.

 

[DonQuichote]

The NoData event is only meant for reports that contain no data at all (empty table / query)

I guess the Nz([male],0) function is the best solution here. There are two ways:

create a query, based on the table, that returns 0 where the table returns Null (using the Nz function) and base your crosstab on that one

If the sum is a total (not a subtotal per page), you might consider calculating the sum indepently of the report with a separate query (SELECT Count([Male] FROM your_table WHERE [Male]=1)