Summing IIf Statements in Access

[motherof2]

I have been really stumped on this issue.....

I need to take salary amounts and cap them at $60,000 (for anyone who earns above that). So, I have used an IIf Statement that looks like this:

=IIf([report salary]>=60000,"$60,000",[report salary])

However, my dilema is that I also need to take the result of the statement and use it in a calculation to obtain a total of all salaries. Access does not seem to recognize the result of the IIf Statement as a 'value'. My total gives me their orginial salary amount and does not cap it at $60,000.

Is there something else I should be using to achieve my final result?

Thank you in advance for any advice!

Stumped

 

[grnzbra]

why are there quotes around the $60,000. perhaps if you just gave it 60000.

If that doesn't work, try doing the same thing with a function

Public Function CappedSal(reportsalary as currency) as currency
If reportsalary > 60000 then
cappedsal = 60000
else
cappedsal = reportsalary
EndIf
End Function

 

[grnzbra]

what does your summing calculation look like?

 

[joshuar]

Also try ccur(IIf([report salary]>=60000,"$60,000",[report salary]))

This should cast the results as currency.

-Joshua

 

[motherof2]

Thank you so much for your responses!!!!

Here is what I have tried and here were the results:

1) I took out the quotes and the comma around the 60000, the sum calculation still did not pick up this number. It is still picking up the original salary amount.

2) I tried adding the CCur to see if the system would recognize the currency. It did seem to recognize the figure as currency, as it put the dollar sign in front of the 60000 along with the comma in the appropriate place. However, the sum calculation is still picking up the orginial salary amount and not the 60000.

3) I tried using the Public Function CappedSal. Access automatically put brackets around [cappedsal] and then did not recognize it when running the report.

My theory now is that maybe it is in my calculation, which looks like this:

=Sum(Nz([report salary]))

I have found my solution!!!!!!!!!
Here is what I had to change my sum calculation to:

=Sum(Nz(IIf([report salary]>=60000,60000,[report salary])))

After taking out the quotes around the 60000 and changing my sum calculation, it FINALLY WORKS!!!!

Thank You!!!!