0

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!

Thomas
  • 1,445
  • 14
  • 30
Evert Lizama
  • 11
  • 4
  • although @sergeys provided a good answer you can also consider a custom function to satisfy your requirement. It's hit or miss which will be quicker for a small dataset but custom functions are slower than direct SQL. Just pass in all of your values and return the desired results. I find this method easier for me to understand what I am doing. – acr_scout Jan 26 '18 at 00:47

1 Answers1

1

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.

Sergey S.
  • 6,296
  • 1
  • 14
  • 29