I'm new to the forum and I have a question about IIf statments. I keep recieving an error message stating, "...contains invalid syntax." Acess then points at the AND operator, is there something wrong with the syntax?

LSBO-FHA-NUMBER: IIf(([tblUpdateTrimLoanrow].[LNTYPE])="1",[tblUpdateTrimLoanrow].[MTGPCN],(IIf(AND(([tblUpdateTrimLoanrow].[LNTYPE])="3",([tblUpdateTrimLoanrow].[MTGINS]<>"9")),[tblUpdateTrimLoanrow].[MTGPCN]," ")))

I'm new to the forum and I have a question about IIf statments. I keep recieving an error message stating, "...contains invalid syntax." Acess then points at the AND operator, is there something wrong with the syntax?

LSBO-FHA-NUMBER: IIf(([tblUpdateTrimLoanrow].[LNTYPE])="1",[tblUpdateTrimLoanrow].[MTGPCN],(IIf(AND(([tblUpdateTrimLoanrow].[LNTYPE])="3",([tblUpdateTrimLoanrow].[MTGINS]<>"9")),[tblUpdateTrimLoanrow].[MTGPCN]," ")))

Is this a problem with an Excel Sheet? I do not believe that Access is able to use the AND statement that way

You need some type of evaluation before the AND or it needs to be removed.

I revised the code:

LSBO-FHA-NUMBER: IIf(([tblUpdateTrimLoanrow].[LNTYPE])="1",[tblUpdateTrimLoanrow].[MTGPCN],(IIf(([tblUpdateTrimLoanrow].[LNTYPE])="3" AND IIf([tblUpdateTrimLoanrow].[MTGINS]<>"9"),[tblUpdateTrimLoanrow].[MTGPCN]," "))," ")

Now the error message is, "...containing the wrong number of parameters."

You now have to many IIF()'s

Maybe this is what you want:


LSBO-FHA-NUMBER: IIf(([tblUpdateTrimLoanrow].[LNTYPE])="1", [tblUpdateTrimLoanrow].[MTGPCN], IIf( ([tblUpdateTrimLoanrow].[LNTYPE])="3" AND ([tblUpdateTrimLoanrow].[MTGINS]<>"9") , tblUpdateTrimLoanrow].[MTGPCN], " ") )

Well this is the latest code and the IIf statement is still not evaluating the False statement on the first IIf statement.

LSBO-FHA-NUMBER: IIf(([tblUpdateTrimLoanrow].[LNTYPE])="1",[tblUpdateTrimLoanrow].[MTGPCN],IIf([tblUpdateTrimLoanrow].[LNTYPE]="3" And IIf([tblUpdateTrimLoanrow].[MTGINS]<>"9",[tblUpdateTrimLoanrow].[MTGPCN],"0000000000"),"123456789"))

I placed zero's to tell which false statement was being evaluated.

A Standard IIf Statement has three parts.
IIf(Condition to Test, Action/Result if Test=True, Action/Result if Test=False)

Evaluation of your SQL Code (See Below) with this in mind, yields the following:

The OuterMost IIf Statement appears to have the three required parts (Condition, Result, Action)
The Middle IIf Statement appears to be missing one of its Result/Action Clauses.
The InnerMost IIf Statement appears to have the three required parts (Condition, Result, Result)
Feel free to make any necessary modifications to my interpretation of the intent of the SQL code in the event that it is incorrect.

IIf(([tblUpdateTrimLoanrow].[LNTYPE])="1",
[tblUpdateTrimLoanrow].[MTGPCN],
IIf([tblUpdateTrimLoanrow].[LNTYPE]="3" And
IIf([tblUpdateTrimLoanrow].[MTGINS]<>"9",
[tblUpdateTrimLoanrow].[MTGPCN],
"0000000000"),
"123456789"))

There is only two IIf statements, the second is a, "if this and this is true then this, statement."

There is only two IIf statements, the second is a, "if this and this is true then this, statement."

Some points to Ponder:

Your sample code had Three IIf Statements (see Below). All I did was provide an interpretation.
The standard VBA Syntax If/Then/Else is not valid in this context.
Please break out what you expect the context to be. Perhaps we can help you to reshape it.

This how I want the if statement to evaluate.


iif tblUpdateTrim.LNTYPE = 1

True:
tblUpdateTrimLoanrow.MTGPCN


False:
iif tblUpdateTrimLoanrow.LNTYPE="3" And tblUpdateTrimLoanrow.MTGINS<>"9"

True:
tblUpdateTrimLoanrow.MTGPCN

False:
10 blank spaces (I placed zero's, for the final false statement for you can't see blank spaces.)

Try this and see if it does what you are looking for:

IIf(([tblUpdateTrimLoanrow].[LNTYPE])="1",
[tblUpdateTrimLoanrow].[MTGPCN],
IIf(([tblUpdateTrimLoanrow].[LNTYPE]="3" And
[tblUpdateTrimLoanrow].[MTGINS]<>"9"),
[tblUpdateTrimLoanrow].[MTGPCN],
"123456789"))

This sql code doesn't evaluate any other numbers except the 1 and th 3 with the not equal to 9.

Hi -

Consider the Switch() function. The logic is more apparent and you'll not get hung up matching the Iif()'s, parenthesis, etc..

LSBO-FHA-NUMBER: Switch([tblUpdateTrimLoanrow].[LNTYPE])="1", [tblUpdateTrimLoanrow].[MTGPCN],
[tblUpdateTrimLoanrow].[LNTYPE]="3" And [tblUpdateTrimLoanrow].[MTGINS]<>"9", tblUpdateTrimLoanrow.MTGPCN,
True, "0000000000")

Bob