If Statements

[user]

Hello

I need to write an if statement which will show when 2 conditions are met
iif([name]="team" &[city]="new york",1,iif([name]="team"&[city]=new
jersey",2",""))

I want the results to display "1" if name is team and city is ny
and "2" if name is team and city is nj.

I am getting the error of wrong number of functions entered.

Please help.

 

[Jellifish]

iif([name]="team",switch([city]="new york",1,[city]="new jersey",2),"")

The SWITCH function is your best bet in this situation.

 

[Jerry Whittle]

As Team is used by both, we can check for it up front without the &. Actually
it think that ampersands won't work anyway. You probably need an And. See if
this works:

IIf([name]="team", IIf([city]="new york",1, IIf([city]="new jersey",2,"")))

Also instead of "" as the last argument for all others not matching, you
might consider using Null (with no quotes around it).

 

[fredg]

Hello

I need to write an if statement which will show when 2 conditions are met
iif([name]="team" &[city]="new york",1,iif([name]="team"&[city]=new
jersey",2",""))

I want the results to display "1" if name is team and city is ny
and "2" if name is team and city is nj.

I am getting the error of wrong number of functions entered.

Please help.

There is a difference between the operator word "AND" and the
ampersand (&) as and.
In this case you must use the word "AND".
You also have a misplaced " after the 2 and are missing one in front
of new jersey.

Using an unbound text control, set it's control source to:

= iif([name]="team" AND [city]="new york",1, Iif([name]= "team" AND
[city]="new jersey",2,""))

** Make sure the name of this control is NOT "Name" or "City". **

Note: Name is a reserved Access/VBA/Jet word and should not be used as
a field name.
For additional reserved words, see the Microsoft KnowledgeBase article
for your version of Access:

109312 'Reserved Words in Microsoft Access' for Access 97
209187 'ACC2000: Reserved Words in Microsoft Access'
286335 'ACC2002: Reserved Words in Microsoft Access'
321266 'ACC2002: Microsoft Jet 4.0 Reserved Words'

For an even more complete list of reserved words, see:
http://www.allenbrowne.com/AppIssueBadWord.html