Calculated Fields in a Query

[Nightowl4933]

Well, I thought this would be easy...

I have 4 fields in a query, from which I would like to calculate a final figure. The fields are formatted as Currency, are in the same table and are as follows:

(F)und - The organisation I'm getting funding from
(S)ecured - Funding I've been promised
(R)eceived - Funding I've already received
(H)eld - Funding being held until I ask for it.

What I'm trying to do is have a calculated field which is [Secured] - ([Held] + [Received]) and Grouped by [Fund], but the calculation doesn't give the correct result.

The values involved are:
(F),(S),(R),(H),Result
John, £684, £193, £15, £476 (684 - (193 + 15) = 476) Correct
Paul, £6, £6, £0, £0 (6 - (6 + 0) = 0) Correct
George, £54, £54, £0, £0 (54 - (54 + 0) = 0 Correct
Ringo, £97, £21, £0, £0 (97 - (21 + 0) = 0 Wrong

The actual formula in the Query is:

Result: [Secured]-([Received]+[Held]) although, to be more accurate, the '0' results are blank. When I take [Held] out of the equation, i.e. Result: [Secured] - [Received], it works properly.

Help! (Which is also the name of one of their earliest albums and movies!)

 

[The_Doc_Man]

Problem may be not if blank but if null.

Perhaps if you used the "NZ" function in your formula you might catch any bad cases.

x = NZ([field], value-if-null) - and you can put any kind of value here because NZ will return a variant; just remember that if you return text but X needs a number, you have to assure that you correctly convert the text to the right format first. For example,

x = CCurr( NZ( [Field], 0 ) ) - will guarantee that the value on the right-hand-side of that expression ends up as Currency.