IIf Statement problem.

[mattfox]

I am using Access 97 to create a payroll program for our contractors.

The problem that I am having is calculating escrow. This is how it works - If the gross is less than or equal to $500 then $50 gets subtracted otherwise gross*0.1. The max amount subtracted for a year is $1250. These are the IIf statements I used :

Escrow – =IIf((Sum(IIf(([Gross]<=500),50,([Gross]*0.1))))>=1250,0,IIf(([Gross]<=500),50,([Gross]*0.1)))
Escrow Total – =IIf((Sum(IIf(([Gross]<=500),50,([Gross]*0.1))))>=1250,1250,Sum(IIf(([Gross]<=500),50,([Gross]*0.1))))

This obviously halfway works. The problem that occurs is that once the total escrow hits $1250, it replaces all the previous calculations in the previous records. I need this information to stay, so it can go in my yearly earnings statement. At this point I am lost. Hope somebody can help.


Thanks,

Matt

 

[Miners47]

This is assuming that you are grouping on your user ID's:

Escrow = IIf(Sum([Gross])<=500, &quot;50&quot;, IIf(Sum([Gross]>=500, Sum([Gross])*0.1, IIf((Sum[Gross]*0.1)>= 1250, &quot;1250&quot))

I think that this will fix your problem.

 

[mattfox]

This doesn't work. You have to have a true and a false after the expression in an IIf statement.

 

[Guest_imported]

That isn't always true. If you want something to happen when the statement is false then you can specify it, but you don't have to. The statement I wrote is like an if-then-else statement.

 

[dneufarth]

I don't think Miner47's formula limits the max to 1250.

Try Escrow=IIf(Sum([Gross])*0.1>1250,1250,IIf(Sum([Gross])<=500,50,Sum([Gross])*0.1]))

Dave

 

[mattfox]

Neither of those work. I get &quot;#Name&quot; in the field.

 

[dneufarth]

Conducted a small test. After creating a test table with 'name' and 'gross' columns and a corresponding tabular autoform, I placed three text box controls in the footer section of the form. One control summed [gross], one used escrow formula from my previous post, and one just counts the [name] field.

All worked okay.

Went back and created an error in one of the controls (mispelled field name). All text box controls in footer section now have #Name error. Don't know why they all have this error as they do not reference one another.

Could you have another control with incorrect field names?

Dave

 

[raskew]

Rather than a series of nested Iif statements, which can become increasingly complicated, you might consider the Switch() function. Read about it in the help file. In your example, it'd appear something like this:

Switch([gross]<=500,50,[gross]>12500,1250,True,[gross]*0.1)

It works like the Select Case, acting on the first true statement.
It determines if [gross]<= 500 and, if so, returns 50, else
It determines if [gross]>12500 and, if so returns, 1250, else
It returns [gross] * 0.1

If your table consisted of three currency fields (Gross, Escrow and EscrowTotal) and (I'm assuming here) that Escrow was a monthly fee, you could fill the Escrow and EscrowTotal fields using this update query:

UPDATE tblEscrow SET tblEscrow.escrow = Switch([gross]<=500,50,[gross]>12500,1250,True,[gross]*0.1), tblEscrow.EscrowTotal = IIf([Escrow]*12>1250,1250,[escrow]*12);

Don't believe the name error is related. It's probably, as suggested, a mistyped field name.

 

[raskew]

Rather than a series of nested Iif statements, which can become increasingly complicated, you might consider the Switch() function. Read about it in the help file. In your example, it'd appear something like this:

Switch([gross]<=500,50,[gross]>12500,1250,True,[gross]*0.1)

It works like the Select Case, acting on the first true statement.
It determines if [gross]<= 500 and, if so, returns 50, else
It determines if [gross]>12500 and, if so returns, 1250, else
It returns [gross] * 0.1

If your table consisted of three currency fields (Gross, Escrow and EscrowTotal) and (I'm assuming here) that Escrow was a monthly fee, you could fill the Escrow and EscrowTotal fields using this update query:

UPDATE tblEscrow SET tblEscrow.escrow = Switch([gross]<=500,50,[gross]>12500,1250,True,[gross]*0.1), tblEscrow.EscrowTotal = IIf([Escrow]*12>1250,1250,[escrow]*12);

Don't believe the name error is related. It's probably, as suggested, a mistyped field name.

 

[mattfox]

The switch expression worked, but now my problem is getting Escrow total to work. I get that &quot;#Name&quot; when I use - IIf([Escrow]*12>1250,1250,[Escrow]*12).

 

[mattfox]

Nevermind about my last post. It works. The problem is that now with the switch statement after 1250 is taken out of the guy's pay it shows 1250 in that field for the next records. So in my YTD field 1250 is subtracted from the gross instead of nothing. How can I make it show 0?