Access IFF statement help

Posted on 2007-07-25
Last Modified: 2008-01-09
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.
Question by:Rusty Warning
    LVL 61

    Accepted Solution

    Try this...

    Sum(IIf([tblCurrent]![Date Paid] Is Not Null,[tblCurrent]![Fee], 0))
    LVL 75

    Expert Comment

    by:DatabaseMX (Joe Anderson - Access MVP)

     How about:

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

    LVL 61

    Expert Comment

    >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))

    Author Comment

    by:Rusty Warning
    Thank You it worked. I see where I went wrong.
    LVL 61

    Expert Comment

    Glad to help out :-)

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    Join & Write a Comment

    Suggested Solutions

    In the previous article, Using a Critera Form to Filter Records (, the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
    It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
    In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now