Calculations in report not working.

[Christopherusly]

I have a report which pulls in data from three related tables, the fields are:

Job NO
Job Title
Spend
Estimated Fee
FD3.6 Fees
Fee Remaining
% Spend

of these fields, the last three are caculated on the report. Now the problem i am having which occurs at both query and report level is that when adding [Estimated fee] & [FD3.6 Fees], if there is a value for FD3.6 Fee, i get the summed calculation, however if there is not i do not get a value at all ... I am stumped as to why access is doing this.

Any suggestions you guys ?

 

[vbaInet]

What is the value of [Estimated fee] when there is no fd.36 fee value?

 

[PNGBill]

Also, check you queries for Null's.

Your Report will accept Nulls but if you want to also use this in a Calculated Control then you may have problems.

 

[Christopherusly]

What is the value of [Estimated fee] when there is no fd.36 fee value?

There will always be a value for [estimated fee] this can vary from £30 to a couple of grand, but it is not always the case that there is an FD3.6 fee to go with [estimated fee]

The way round this is to assign a zero value to every live project in the FD3.6tbl, but this is not an idea way of dealing with things

 

[Christopherusly]

Also, check you queries for Null's.

Your Report will accept Nulls but if you want to also use this in a Calculated Control then you may have problems.

Okay, the concept of NULL is new to me, would you mind elaborating further PNGBill, many thanks.

I attached examples of the query showing when there is a [FD3.6 Fee] value and [Estimated Fee] i guess part of the problem is the relationship between the tables where on the relationship is set to option 3 - Include all reocrds from qry_Selectquery2 and only those records from tbl_early warning time calc where the joined fiels are equal.

Chris

 

[Christopherusly]

Short read of the Acces 2003 bible later and we have the following expression incorporating NULL

=NZ([qry_selectquery2.SumOfCost],0)+NZ([Sum Of Cost],0)-NZ([qry_selectquery.SumOfCost],0)

ahhhh you have shown me the way thanks guys adds kudos