Eliminating blank fields in access 2003 report

[reknox]

I now need help with a report. My last post was in the query section related to joining several queries and getting the correct result. By the way BrianWarnock, I am taking your advice on the look up field in the table and removing it. I had read the pros and cons about this on several sites and it did seem controversial. Now on the report, in the columns for each quarter, at times there will be no data because the individual made no contribution for the specific time period. It's not that the value is missing; there is none. Can this be converted to 0 formatted to currency rather than a blank space i.e. $0.00. If so, how. Example below: Thanks again.

Name
Address
City


Qtr1: $400.00
Qtr2:
Qtr3: $ 25.50
Qtr4: $150.00

 

[pbaldy]

You can try the Nz() function (more info in help).

 

[reknox]

I read the help as you suggested but it's way over my head. I can get a 0 with using the following in the report control: =IIf(IsNull([Qtr2]),"0",Format([Qtr2],"Currency")). Even though I set the format control as currency and decimal as 2, unless I add it as shown in the expression, it won't show as currency. I'm not asking you to do the code but will you help me understand better than the help file. Thanks.

 

[pbaldy]

Does this not work?

=Nz([Qtr2], 0)

 

[pbaldy]

By the way, it also occurs to me that you could use the format property of the textbox. For numeric fields, it can have 4 parts, one of which covers Null values.

 

[reknox]

Yes what you gave me worked. Thanks a lot. I'm always amazed at how simple things turn out when it seemed so difficult in the beginning. Will you elaborate a little on your last suggestion regarding the 4 parts. Which one covers the null value?

 

[pbaldy]

The 4th (positive, negative, zero, null). Here's the example from help

$#,##0.00[Green];($#,##0.00)[Red];"Zero";"Null"