Trying to build query with calculated field using multiple table fields.

[bravura]

TOTAL_REVENUE: ([Total_MT]*[MTON_RATE])+([NbrOfD20Bkd]*[D20_RATE])+([NbrOfD40Bkd]*[D40_RATE])

or TOTAL_REVENUE: (([Total_MT]*[MTON_RATE])+([NbrOfD20Bkd]*[D20_RATE])+([NbrOfD40Bkd]*[D40_RATE]))


Above returns null value. if i select any one of the three calculations, it runs ok even if i add specific numbers, but any two or all three together don't work.

all field types are the same (Three are integer, three are currency)


ex.

Calculation and Return

TOTAL_REVENUE: ([Total_MT]*[MTON_RATE]) = $100
TOTAL_REVENUE: ([Total_MT]*[MTON_RATE]) + 1000 = $1100

TOTAL_REVENUE: ([NbrOfD20Bkd]*[D20_RATE]) = $100
TOTAL_REVENUE: ([NbrOfD40Bkd]*[D40_RATE]) = $100

and so on, you get it


but


TOTAL_REVENUE: ([Total_MT]*[MTON_RATE])+([NbrOfD20Bkd]*[D20_RATE])+([NbrOfD40Bkd]*[D40_RATE]) = null

 

[bravura]

sold by weight (MTON) or by unit (D20 and/or D40). someone on another board pointed out that if any one of three has null value, return will be null.

 

[bravura]

fixed. i created separate query. iif(isnull(... then zero. then used that for rev calc.
I prob didn't need separate query but may need this for future calcs in the future.