Expression Builder Urgent

[vasque01]

Hi Everyone,

I am trying to build an expression in my Access database. At the moment I have the one bellow, but when I want to save it I get an error message saying that the "expression has a fundtion containing the wrong number of arguments", does anyone has a suggestion, or maybe can spot what is wrong in the one I build?

IIf([Partner Channel] Is Null,"",IIf([Partner Channel]="Agent" Or [Partner Channel]="Agents","Yes", IIf(([Partner Group]="Consumer","No",IIf(([Partner Group]="OEM","No",IIf(([Partner Channel]="B2B" And [Partner type]="Professional Trade (Project Partner)","No",IIf(([Partner Channel]="B2G" And [Partner Philips Sales Threshold]="Yes"),"Yes",IIf(([Partner CPI]<60 And [Partner Channel]="B2B" And [Partner Philips Sales Threshold]="Yes"),"Yes","No"))))

 

[June7]

IIf([Partner Channel] Is Null,"", IIf([Partner Channel] LIKE "Agent*","Yes", IIf([Partner Group]="Consumer","No", IIf([Partner Group]="OEM","No", IIf([Partner Channel]="B2B" And [Partner type]="Professional Trade (Project Partner)","No", IIf([Partner Channel]="B2G" And [Partner Philips Sales Threshold]="Yes","Yes", IIf([Partner CPI]<60 And [Partner Channel]="B2B" And [Partner Philips Sales Threshold]="Yes","Yes","No")))))))

or

Switch([Partner Channel] Is Null,"", [Partner Channel] LIKE "Agent*","Yes", [Partner Group]="Consumer","No", [Partner Group]="OEM","No", [Partner Channel]="B2B" And [Partner type]="Professional Trade (Project Partner)","No", [Partner Channel]="B2G" And [Partner Philips Sales Threshold]="Yes","Yes", [Partner CPI]<60 And [Partner Channel]="B2B" And [Partner Philips Sales Threshold]="Yes","Yes, True,"No")

or

IIf([Partner Channel] Is Null,"", IIf([Partner Channel] LIKE "Agent*"
Or ([Partner Channel]="B2G" And [Partner Philips Sales Threshold]="Yes")
Or ([Partner CPI]<60 And [Partner Channel]="B2B" And [Partner Philips Sales Threshold]="Yes'), "Yes",
IIf([Partner Group]="Consumer"
Or [Partner Group]="OEM"
Or ([Partner Channel]="B2B" And [Partner type]="Professional Trade (Project Partner)","No", "")))

or

Switch([Partner Channel] Is Null,"", [Partner Channel] LIKE "Agent*"
Or ([Partner Channel]="B2G" And [Partner Philips Sales Threshold]="Yes")
Or ([Partner CPI]<60 And [Partner Channel]="B2B" And [Partner Philips Sales Threshold]="Yes"), "Yes",
[Partner Group]="Consumer"
Or [Partner Group]="OEM"
Or ([Partner Channel]="B2B" And [Partner type]="Professional Trade (Project Partner)","No", True,"")


Are any of those fields Yes/No data type?

 

[isladogs]

Welcome to AWF

I've moved your post to the VBA section as it contains code
The Introduce Yourself forum is intended for new users to do just that

Using this many nested IIf statements its easy to make mistakes
You have several left double brackets after IIf((
Hopefully this is correct but its hard to check without having the application in front of me. Check the number of ( and ) brackets match.
I think there are 7 IIfs so you need 7 of each after removing extraneous brackets around Or & And statements

IIf([Partner Channel] Is Null,"",IIf([Partner Channel]="Agent" Or [Partner Channel]="Agents","Yes", IIf([Partner Group]="Consumer","No",IIf([Partner Group]="OEM","No",IIf([Partner Channel]="B2B" And [Partner type]="Professional Trade (Project Partner)","No",IIf([Partner Channel]="B2G" And [Partner Philips Sales Threshold]="Yes","Yes",IIf([Partner CPI]<60 And [Partner Channel]="B2B" And [Partner Philips Sales Threshold]="Yes","Yes","No")))))))

However I would recommend you find better ways of dealing with this kind of situation
e.g. use a function and Select Case statements
Google "replace nested IIf Access VBA"

 

[vasque01]

Hi,

It works now, thank you so much!

 

[isladogs]

Hi

You haven't sad which solution you used but I would definitely avoid nested IIfs in future where you have more than three.

 

[vasque01]

Hi,

I used this one:
IIf([Partner Channel] Is Null,"",IIf([Partner Channel]="Agent" Or [Partner Channel]="Agents","Yes", IIf([Partner Group]="Consumer","No",IIf([Partner Group]="OEM","No",IIf([Partner Channel]="B2B" And [Partner type]="Professional Trade (Project Partner)","No",IIf([Partner Channel]="B2G" And [Partner Philips Sales Threshold]="Yes","Yes",IIf([Partner CPI]<60 And [Partner Channel]="B2B" And [Partner Philips Sales Threshold]="Yes","Yes","No")))))))

Now it works perfectly!