Link to home
Start Free TrialLog in
Avatar of Rusty Warning
Rusty WarningFlag for United States of America

asked on

Access IFF statement help

I am having trobles with this Iff statement

Amt Recieved: Sum([tblCurrent]![Fee]+IIf([tblCurrent]![Date Paid] Is Not Null,[tblCurrent]![Date Paid],0))

I am trying to find the sum of the fees where there is a Date in the Date Paid field. It runs but comes up with wrong numbers.
ASKER CERTIFIED SOLUTION
Avatar of mbizup
mbizup
Flag of Kazakhstan image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial

 How about:

Sum([  Nz(tblCurrent]![Fee]) + Nz([tblCurrent]![Date Paid]) )

mx
>sum of the fees where there is a Date in the Date Paid field
The problem with the code you posted seems to be that you are:
 - Testing for a null date
 - Summing Date and Fees
I beleive this is why the numbers look wrong.

What you should be doing is:
- Testing for null dates
- Summing fees where dates are not null

This should do it:
Sum(IIf([tblCurrent]![Date Paid] Is Not Null,[tblCurrent]![Fee], 0))

or alternatively:
Sum(IIf(IsDate([tblCurrent]![Date Paid]) = TRUE ,[tblCurrent]![Fee], 0))
Avatar of Rusty Warning

ASKER

Thank You it worked. I see where I went wrong.
Glad to help out :-)