Access IFF statement help

Posted on 2007-07-25
Medium Priority
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
  • 3
LVL 61

Accepted Solution

mbizup earned 500 total points
ID: 19565633
Try this...

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

 How about:

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

LVL 61

Expert Comment

ID: 19565853
>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
ID: 19565872
Thank You it worked. I see where I went wrong.
LVL 61

Expert Comment

ID: 19565883
Glad to help out :-)

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Suggested Courses

807 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