CODEWITHSUNDEEP
databasems-accessif-statementnestedexpressionbuilder
Evert Lizama
Evert Lizama

Reputation: 11

Access Database - Nested If statements

I am currently trying to create a nested if statement field in an access database that fills data based on criteria it has to meet.

So far this is what I have in the expression builder:

IIf([JDE Aging Report].[Term]="PPL" And [JDE Aging Report].[OpenAmount]>0,"PPL Sale", 
IIf([JDE Aging Report].[Term]="PPL" And [JDE Aging Report].[OpenAmount]<0,"PPL Credit", 
IIf([JDE Aging Report].[Term]="AE" Or "DSC" Or "MC" Or "VSA" And [JDE Aging Report].[OpenAmount]<0,"CC Credit",
 **IIf([JDE Aging Report].[Term]="AE" Or "DSC" Or "MC" Or "VSA" And [JDE Aging Report].[OpenAmount]>1,"CC Sale"))))**

For some reason it is not picking up the last parameter in the expression (in bold) and as a result the data that is being updated is incorrect.

Can someone tell me what I am doing wrong? Is there a limitation when it comes to building nested if statements in an access database?

Many thanks!

Upvotes: 0

Views: 2565

Answers (1)

Sergey S.
Sergey S.

Reputation: 6336

Last Iif should be 

IIf(([JDE Aging Report].[Term]="AE" Or 
[JDE Aging Report].[Term]="DSC" Or 
[JDE Aging Report].[Term]="MC" Or 
[JDE Aging Report].[Term]="VSA") And 
[JDE Aging Report].[OpenAmount]>1,"CC Sale")

or

IIf([JDE Aging Report].[Term] In ("AE", "DSC", "MC", "VSA") And 
[JDE Aging Report].[OpenAmount]>1,"CC Sale")

Similar change should be done for previous Iif.

Also make sure that fields used in conditions are not null, otherwise results may be incorrect.

Upvotes: 1

Related Questions