report null value question

[psimon1]

quick question in Access, my guru...

My report calc is erroring out because it's based on a value that is null.

In other words, d= A + B +C (a, b, and c are based on subreports).

However, the value for C is derived and is null. (It doesn't show up on the subreport because the employee doesn't have that benefit.

I have played with the null values (if C is null, then 0, else [c]) but it's not working. In short, I want C not to equal 'error'--which causes D to error out.

Is there a formula that will take the value of C and make it 0 if it's null?

Any ideas? Thanks in advance.

 

[CosmoKramer]

Try:

Nz([C],0)

A common mistake that people make when trying to design something completely foolproof is to underestimate the ingenuity of complete fools.
Douglas Adams

 

[psimon1]

Thanks. I tried that before but I am having a problem.

 

[CosmoKramer]

What kind of problem?? Please post the code here that you have tried....



A common mistake that people make when trying to design something completely foolproof is to underestimate the ingenuity of complete fools.
Douglas Adams

 

[psimon1]

It's kind of hard to explain.

Essentially, the query returns no values because the employee doesn't have this type of benefit. This query is part of the subreport.

I need a calc that supplies a zero when there are no rows for a query. If the query returns rows, then employees have this benefit, and I need the calculation to return the 'annual amount' value.

Nz I tried but it didn't work.

 

[jrbarnett]

Psimon1,

I assume your main report is trying to access the subreport value when it doesn't exist, so try something like:

=Iif(IsNull(Subreportname.Report!txtControlname),0,Subreportname.Report!txtControlname)

Put this on a textbox on the main report, but set the visible property to false.
You can then use the Nz function in the main report based on this hidden textbox value.

An alternative would be to use the domain functions
DSum/DCount/DAvg/DMin/DMAx etc to bring the appropriate value in directly.

John

 

[dhookom]

Nz() will not work on a control that doesn't exist. If you have a subreport with no data, you could use something like:

=IIf(SubRptCtlName.Report.HasData, SubRptCtlName.Report!txtControlname, 0)


Duane
MS Access MVP