Rusty Warning
asked on
Access IFF statement help
I am having trobles with this Iff statement
Amt Recieved: Sum([tblCurrent]![Fee]+IIf ([tblCurre nt]![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.
Amt Recieved: Sum([tblCurrent]![Fee]+IIf
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>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))
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
or alternatively:
Sum(IIf(IsDate([tblCurrent
ASKER
Thank You it worked. I see where I went wrong.
Glad to help out :-)
How about:
Sum([ Nz(tblCurrent]![Fee]) + Nz([tblCurrent]![Date Paid]) )
mx