calculated field in query 1

[samotek]

In my current query i have to sum three separate sums Bonus: [BonusL1]+[BonusL2]+[BonusL3]. All these fields are calculated fields.However i get no results probably because sometimes only one field is filled in, for example only BonusL3, while the other two are blank.Maybe this is the reason why i get no results.Could you help me ? Also, the sums should be summed up only when the Yes/No field getBonus in the same query is set to Yes.

 

[lespaul]

look into the NZ function, maybe here in this query, you may need to use it in the prior one...hard to tell:

Bonus: NZ([BonusL1], 0) + NZ([BonusL2], 0) +NZ([BonusL3],0)

Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com

 

[samotek]

Yes, thank you, the NZ function works perfect! How could i build the If condition, to calculate only when the field GetBonus in the same query is set to Yes? This field is Yes/No field and i want to calculate only when this field is Yes

 

[dhookom]

Leslie,
You must be in good holiday spirits since you didn't mention anything about having multiple bonus fields and possibly needing normalizing ;-)

Duane MS Access MVP
Now help me support United Cerebral Palsy

 

[lespaul]

duane,
My New Year's Resolution (a little early) is giving up on trying to get people to see the error of their ways....



sam,
what's the SQL of the query?

Leslie

 

[samotek]

Thank you to Leslie!!!!! the calculations with NZ are OK thank you !
I have a syntax error when trying to calculate with IIF. Would you have a look ?

Bonus : iif(getBonus = -1,sum(nz(BonusL1;0+ NZ([BonusL2];0+ NZ([BonusL3;0),0)

 

[lespaul]

try:

Bonus : iif(getBonus = -1,sum(nz([BonusL1],0) + NZ([BonusL2],0)+ NZ([BonusL3],0),0))

 

[PHV]

Bonus : IIf(getBonus = -1; Sum(Nz(BonusL1;0[!])[/!] + Nz(BonusL2;0[!])[/!] + Nz([BonusL3;0)[!])[/!]; 0)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886