Trying to use IIF and AND together but getting an error

[jferguson]

Hello All. I am trying to use the following, but the result is "Error" in the field instead of the number of days:

daysToFirstPmt2: IIf([Date Closed]<>"" And [First Payment Date]="","1000",IIf([Date Closed]="" And [First Payment Date]="",Date()-[Date Entered],[First Payment Date]-[Date Entered]))

Any help would be greatly appreciated.

Thanks,
J

[orange]

You may get more responses if you describe WHAT you are trying to do in plain English -- not a quasi expression.

You are dealing with Date data types so you should look at the various Date related functions.

There are function definitions and samples at http://www.techonthenet.com/access/functions/index.php
Look art the date functions.

I suggest you research Naming Convention and not use spaces and special characters in field and object names.

[June7]

Why are you testing for empty string? Is it possible for any of those fields to be Null?

[John_G]

Hi -

I see two possible issues -

First, you want the number of days, so in this bit :

IIf([Date Closed]<>"" And [First Payment Date]="","1000".... , the 1000 should not be in quotes.

Second, in this bit:

IIf([Date Closed]="" And [First Payment Date]="",Date()-[Date Entered],[First Payment Date]-[Date Entered]))

If [date closed] is NOT = "", but [First Payment Date] IS = "", then what is [First Payment Date]-[Date Entered] ,
because that is what the IIF will give you.

One caveat worth mentioning: As I recall, the IIF evaluates ALL expressions in it, regardless of the conditions,
so if any of the expressions generates an error, the IIF will fail.

This is an important difference between an IIF and an If - Then - Else - Endif construct.

Also, as June7 has pointed out, you should consider the possibility that your fields
are Null, instead of "" - they are not the same.

HTH

John