Criteria (If Null)

[paulcherianc]

Where are my wrong with this formula. Help please!!

TotA: IIf(IsNull([Basic Salary EUR]),0,([Basic Salary EUR])>0,([Basic Salary EUR])*IIf(IsNull([ZZB-Exchange Rate Table]![Eur Exchange Rate]),0,[ZZB-Exchange Rate Table]![Eur Exchange Rate])+IIf(IsNull([Allowance A]),0,[Allowance A]),IIf(IsNull([Basic Salary QAR]),0,[Basic Salary QAR])+IIf(IsNull([Allowance A]),0,[Allowance A])

 

[JHB]

You're missing an IIf here:

([Basic Salary EUR])>0,([Basic Salary EUR])

 

[arnelgp]

suggestion, wrapped in a function.
put in a module:

public sub fnCompute(basicSal, eurXChg, basicSalQAR, AllowA) As Double
    basicsalEUR = Val("0" & basicsal)
    eurXChg = Val("0" & eurXChg)
    allowA = Val("0" & allowA)
    basicSalQAR = Val("0" & basicSalQAR)
    fnCompute = (basicsal * eurXChg) + basicSalQAR + AllowA
End Function

to call from your Query:

TotA: fnCompute([Basic Salary EUR], [ZZB-Exchange Rate Table]![Eur Exchange Rate], [Basic Salary QAR], [Allowance A])

 

[paulcherianc]

Many Thanks Guys!!