formula problem

[Amateur]

I am creating a formula which should do the following:

incentivefeeoption:
Round(IIf((IIf([receivedpremium]>[paidpremium];([receivedpremium]+[paidpremium])*-[incentivefee])) somehow I have to ad

that IIf([receivedpremium]<[paidpremium] then 0

Can someone help me with this?
Thanks
Klaus

 

[Ofer Cohen]

Try

Round(IIf([receivedpremium]>[paidpremium];([receivedpremium]+[paidpremium])*-[incentivefee];0))

 

[Wayne-I-M]

Hi

IIf (([receivedpremium]>[paidpremium],
([receivedpremium]+[paidpremium])*[incentivefee], 0)

You just need to add the 0 as the false answer as the IIf is only this

IIf (Something is true, Insert this, If not insert this)


In your example you have * and - here [paidpremium])*-[incentivefee] so I
have used * you can change this if nes.

 

[Amateur]

I've tried it but it's not working - it's NOT producing a 0 if
receivedpremium is smaller than paidpremium.
Any other idea?
Thanks
Klaus

Try

Round(IIf([receivedpremium]>[paidpremium];([receivedpremium]+[paidpremium])*-[incentivefee];0))

--
Good Luck
BS"D

I am creating a formula which should do the following:

incentivefeeoption:
Round(IIf((IIf([receivedpremium]>[paidpremium];([receivedpremium]+[paidpremium])*-[incentivefee])) somehow I have to ad

that IIf([receivedpremium]<[paidpremium] then 0

Can someone help me with this?
Thanks
Klaus

 

[Ofer Cohen]

Incase one of the fields is null try

Round(IIf(Nz([receivedpremium],0)>Nz([paidpremium]);([receivedpremium]+[paidpremium])*-[incentivefee];0))

--
Good Luck
BS"D

I've tried it but it's not working - it's NOT producing a 0 if
receivedpremium is smaller than paidpremium.
Any other idea?
Thanks
Klaus

Try

Round(IIf([receivedpremium]>[paidpremium];([receivedpremium]+[paidpremium])*-[incentivefee];0))

--
Good Luck
BS"D

I am creating a formula which should do the following:

incentivefeeoption:
Round(IIf((IIf([receivedpremium]>[paidpremium];([receivedpremium]+[paidpremium])*-[incentivefee])) somehow I have to ad

that IIf([receivedpremium]<[paidpremium] then 0

Can someone help me with this?
Thanks
Klaus

 

[Amateur]

Hi Wayne
It's better to send you the whole formula. I entered your part but it's
still the same. It's NOT showin a 0 if receivedpremium < paidpremium

incentivefeetotal:
Round(IIf((IIf([recievedpremium]>[paidpremium];([recievedpremium]+[paidpremium])*-[incentivefee];0))
Is
Null;0;(IIf([recievedpremium]>[paidpremium];([recievedpremium]+[paidpremium])*-[incentivefee];0)));2)

Thanks
Klaus

Hi

IIf (([receivedpremium]>[paidpremium],
([receivedpremium]+[paidpremium])*[incentivefee], 0)

You just need to add the 0 as the false answer as the IIf is only this

IIf (Something is true, Insert this, If not insert this)


In your example you have * and - here [paidpremium])*-[incentivefee] so I
have used * you can change this if nes.

--
Wayne
Manchester, England.

I am creating a formula which should do the following:

incentivefeeoption:
Round(IIf((IIf([receivedpremium]>[paidpremium];([receivedpremium]+[paidpremium])*-[incentivefee])) somehow I have to ad

that IIf([receivedpremium]<[paidpremium] then 0

Can someone help me with this?
Thanks
Klaus

 

[Amateur]

I trie but coming not further. I'll always get the error message "wrong
number of arguments" - but I don't see that.
incentivefeetotal:
Round(IIf(Nz(IIf([recievedpremium];0)>Nz([paidpremium]);([recievedpremium]+[paidpremium])*-[incentivefee];0))
Is
Null;0;(IIfNz([recievedpremium];0)>Nz([paidpremium]);([recievedpremium]+[paidpremium])*-[incentivefee];0)));2)

Thanks
Klaus

Incase one of the fields is null try

Round(IIf(Nz([receivedpremium],0)>Nz([paidpremium]);([receivedpremium]+[paidpremium])*-[incentivefee];0))

--
Good Luck
BS"D

I've tried it but it's not working - it's NOT producing a 0 if
receivedpremium is smaller than paidpremium.
Any other idea?
Thanks
Klaus

Try

Round(IIf([receivedpremium]>[paidpremium];([receivedpremium]+[paidpremium])*-[incentivefee];0))

--
Good Luck
BS"D


:

I am creating a formula which should do the following:

incentivefeeoption:
Round(IIf((IIf([receivedpremium]>[paidpremium];([receivedpremium]+[paidpremium])*-[incentivefee])) somehow I have to ad

that IIf([receivedpremium]<[paidpremium] then 0

Can someone help me with this?
Thanks
Klaus

 

[Amateur]

If I only your formula it's not working neither:
incentivefeetotal:
Round(IIf(Nz([recievedpremium];0)>Nz([paidpremium]);([recievedpremium]+[paidpremium])*-[incentivefee];0))

Incase one of the fields is null try

Round(IIf(Nz([receivedpremium],0)>Nz([paidpremium]);([receivedpremium]+[paidpremium])*-[incentivefee];0))

--
Good Luck
BS"D

I've tried it but it's not working - it's NOT producing a 0 if
receivedpremium is smaller than paidpremium.
Any other idea?
Thanks
Klaus

Try

Round(IIf([receivedpremium]>[paidpremium];([receivedpremium]+[paidpremium])*-[incentivefee];0))

--
Good Luck
BS"D


:

I am creating a formula which should do the following:

incentivefeeoption:
Round(IIf((IIf([receivedpremium]>[paidpremium];([receivedpremium]+[paidpremium])*-[incentivefee])) somehow I have to ad

that IIf([receivedpremium]<[paidpremium] then 0

Can someone help me with this?
Thanks
Klaus

 

[Amateur]

I did read the reply of yours again. You said: "Incase one of the fields is
null try
".
That is not what I mean.
I.E.:
incentivefeeoption:
Round(IIf((IIf([receivedpremium]>[paidpremium];([receivedpremium]+[paidpremium])*-[incentivefee]))

received 4000 paid 3000 than 4000-3000*15%=150
If
received 3000 paid 4000 than 3000-4000*15% = 0

Incase one of the fields is null try

Round(IIf(Nz([receivedpremium],0)>Nz([paidpremium]);([receivedpremium]+[paidpremium])*-[incentivefee];0))

--
Good Luck
BS"D

I've tried it but it's not working - it's NOT producing a 0 if
receivedpremium is smaller than paidpremium.
Any other idea?
Thanks
Klaus

Try

Round(IIf([receivedpremium]>[paidpremium];([receivedpremium]+[paidpremium])*-[incentivefee];0))

--
Good Luck
BS"D


:

I am creating a formula which should do the following:

incentivefeeoption:
Round(IIf((IIf([receivedpremium]>[paidpremium];([receivedpremium]+[paidpremium])*-[incentivefee])) somehow I have to ad

that IIf([receivedpremium]<[paidpremium] then 0

Can someone help me with this?
Thanks
Klaus

 

[Ofer Cohen]

Hi,
The Nz is used to replace Null with 0, and comparison between two fields
when one of them contain Null, will produce the wrong resault, this is why I
sugested usin the Nz, so the Null will be replaced with 0

Round(IIf(Nz([recievedpremium];0)>Nz([paidpremium];0);([recievedpremium]+[paidpremium])*-[incentivefee];0))

What is the field type of paidpremium And recievedpremium, are they numeric?
--
Good Luck
BS"D

I did read the reply of yours again. You said: "Incase one of the fields is
null try
".
That is not what I mean.
I.E.:
incentivefeeoption:
Round(IIf((IIf([receivedpremium]>[paidpremium];([receivedpremium]+[paidpremium])*-[incentivefee]))

received 4000 paid 3000 than 4000-3000*15%=150
If
received 3000 paid 4000 than 3000-4000*15% = 0

Incase one of the fields is null try

Round(IIf(Nz([receivedpremium],0)>Nz([paidpremium]);([receivedpremium]+[paidpremium])*-[incentivefee];0))

--
Good Luck
BS"D

I've tried it but it's not working - it's NOT producing a 0 if
receivedpremium is smaller than paidpremium.
Any other idea?
Thanks
Klaus


:

Try

Round(IIf([receivedpremium]>[paidpremium];([receivedpremium]+[paidpremium])*-[incentivefee];0))

--
Good Luck
BS"D


:

I am creating a formula which should do the following:

incentivefeeoption:
Round(IIf((IIf([receivedpremium]>[paidpremium];([receivedpremium]+[paidpremium])*-[incentivefee])) somehow I have to ad

that IIf([receivedpremium]<[paidpremium] then 0

Can someone help me with this?
Thanks
Klaus

 

[Amateur]

The fields are coming from another query and are numeric.
Thanks
Klaus

Hi,
The Nz is used to replace Null with 0, and comparison between two fields
when one of them contain Null, will produce the wrong resault, this is why I
sugested usin the Nz, so the Null will be replaced with 0

Round(IIf(Nz([recievedpremium];0)>Nz([paidpremium];0);([recievedpremium]+[paidpremium])*-[incentivefee];0))

What is the field type of paidpremium And recievedpremium, are they numeric?
--
Good Luck
BS"D

I did read the reply of yours again. You said: "Incase one of the fields is
null try
".
That is not what I mean.
I.E.:
incentivefeeoption:
Round(IIf((IIf([receivedpremium]>[paidpremium];([receivedpremium]+[paidpremium])*-[incentivefee]))

received 4000 paid 3000 than 4000-3000*15%=150
If
received 3000 paid 4000 than 3000-4000*15% = 0

Incase one of the fields is null try

Round(IIf(Nz([receivedpremium],0)>Nz([paidpremium]);([receivedpremium]+[paidpremium])*-[incentivefee];0))

--
Good Luck
BS"D


:

I've tried it but it's not working - it's NOT producing a 0 if
receivedpremium is smaller than paidpremium.
Any other idea?
Thanks
Klaus


:

Try

Round(IIf([receivedpremium]>[paidpremium];([receivedpremium]+[paidpremium])*-[incentivefee];0))

--
Good Luck
BS"D


:

I am creating a formula which should do the following:

incentivefeeoption:
Round(IIf((IIf([receivedpremium]>[paidpremium];([receivedpremium]+[paidpremium])*-[incentivefee])) somehow I have to ad

that IIf([receivedpremium]<[paidpremium] then 0

Can someone help me with this?
Thanks
Klaus

 

[Wayne-I-M]

Hi

You seem to be going round in circles here. The answer (to your post) is


Answer:IIf
((Nz([TableName]![receivedpremium])>Nz([TableName]![paidpremium]),
(Nz([TableName]![receivedpremium])+Nz([TableName]![paidpremium]))*Nz([TableName]![incentivefee]), 0)

To explain IIf and how it relates to your post.

Let me know if I am wrong
If your field [receivedpremium] contains a greater value that your field
[paidpremium] you want to insert the sum of [receivedpremium] and
[paidpremium] and multiply this new value by the value of your field
[incentivefee].
Plus
If your field [receivedpremium] does "NOT" contain a greater value that your
field [paidpremium] you want to insert 0

This is NOT a nested IIF.
A nested IIF would be something like
If your field [receivedpremium] does "NOT" contain a greater value that your
field [paidpremium] you want to ask if something else is true and if it is
insert the true value (or fumula) - you can add up to 7 nested IIf's in one
forumal

So if all the above is right then use the answer I gave to your very first
post.

IIf (([receivedpremium]>[paidpremium],
([receivedpremium]+[paidpremium])*[incentivefee], 0)

But I would change it slightly to include the table names (and field name
"answer). I have added the Nz function as suggested by Offer (just in case).
_________________________________

Answer:IIf
((Nz([TableName]![receivedpremium])>Nz([TableName]![paidpremium]),
(Nz([TableName]![receivedpremium])+Nz([TableName]![paidpremium]))*Nz([TableName]![incentivefee]), 0)
_________________________________

Just cut and paste this then change the [TableName]'s to what they are and
this will work - if your field names are correct.

Hope this helps