Wrong number of arguments in an expression 4

[rj51cxa]

I have created the following expression in a query:

Year:IIf([BisleyYear]="RNR SKILL AT ARMS MEETING    BISLEY  2000","2000","", IIf([BisleyYear]="RNR SKILL AT ARMS MEETING    BISLEY  2001","2001","", IIf([BisleyYear]="RNR SKILL AT ARMS MEETING    BISLEY  2002","2002","", IIf([BisleyYear]="RNR SKILL AT ARMS MEETING    BISLEY  2003","2003","", IIf([BisleyYear]="RNR SKILL AT ARMS MEETING    BISLEY  2004","2004","", IIf([BisleyYear]="RNR SKILL AT ARMS MEETING    BISLEY  2005","2005","", IIf([BisleyYear]="RNR SKILL AT ARMS MEETING    BISLEY  2006","2006","", IIf([BisleyYear]="RNR SKILL AT ARMS MEETING    BISLEY  2007","2007","", IIf([BisleyYear]="RNR SKILL AT ARMS MEETING    BISLEY  2008","2008","", IIf([BisleyYear]="RNR SKILL AT ARMS MEETING    BISLEY  2009","2009","", IIf([BisleyYear]="RNR SKILL AT ARMS MEETING    BISLEY  2010","2010","")))))))))))

Every time I try to run the query I receive a message box saying:
"The expression you entered has a function containing the wrong number of arguments"

Can anyone please advise me what I am doing wrong.

Thanks a lot
John

 

[fneily]

Microsoft Syntax:
IIf(expr, truepart, falsepart)


IIf([BisleyYear]="RNR SKILL AT ARMS MEETING BISLEY 2000","2000","", IIf([BisleyYear]="RNR SKILL AT ARMS

You have 4 arguments for the IIF. Also, instead of nested if's, you might want to create a little table and do a dlookup or something. It's alot easier to read.

 

[Remou]

Or possible just use Right:
Year: Right([BisleyYear],4)

 

[rj51cxa]

Thanks fneily and Remou for your thoughts.

I worked out why my original code did not work - I should have used "&" not "," after the falsepart. However, I then got the message that the expression was too complex. I tried to simplify it using "Right" as suggested by Remou but again, the expression becomes too complex. Its OK if I reduce the number of IIf statements but that does not give me the number of years that I want.

I'm not sure how to look up from a table in a query so help on that would be much appreciated.

Thanks a lot
John

 

[Golom]

If you reformat it then the stuff in red shouldn't be there.

Year:
IIf([BisleyYear]="RNR SKILL AT ARMS MEETING BISLEY 2000","2000",[COLOR=red]"",[/color]
IIf([BisleyYear]="RNR SKILL AT ARMS MEETING BISLEY 2001","2001",[COLOR=red]"",[/color] 
IIf([BisleyYear]="RNR SKILL AT ARMS MEETING BISLEY 2002","2002",[COLOR=red]"",[/color] 
IIf([BisleyYear]="RNR SKILL AT ARMS MEETING BISLEY 2003","2003",[COLOR=red]"",[/color] 
IIf([BisleyYear]="RNR SKILL AT ARMS MEETING BISLEY 2004","2004",[COLOR=red]"",[/color] 
IIf([BisleyYear]="RNR SKILL AT ARMS MEETING BISLEY 2005","2005",[COLOR=red]"",[/color] 
IIf([BisleyYear]="RNR SKILL AT ARMS MEETING BISLEY 2006","2006",[COLOR=red]"",[/color] 
IIf([BisleyYear]="RNR SKILL AT ARMS MEETING BISLEY 2007","2007",[COLOR=red]"",[/color] 
IIf([BisleyYear]="RNR SKILL AT ARMS MEETING BISLEY 2008","2008",[COLOR=red]"",[/color] 
IIf([BisleyYear]="RNR SKILL AT ARMS MEETING BISLEY 2009","2009",[COLOR=red]"",[/color] 
IIf([BisleyYear]="RNR SKILL AT ARMS MEETING BISLEY 2010","2010","")))))))))))

I agree with Remou though. This is probably easier

IIF (    Right([BisleyYear],4) >= "2000" 
     AND Right([BisleyYear],4) <= "2010", 
     (Right([BisleyYear],4), "")

 

[rj51cxa]

I have just realised why the code was too complex. I was trying to link all the IIF statements using & but putting all the brackets at the end of the expression. If I ended each IIf statement with a bracket and started the next one with &, it worked. e.g.

Year: IIf(Right[BisleyYear],1)="1","2001","")& IIf(Right[BisleyYear],1)="2","2002","")etc

Now I can have as many years as I like. A simple systax error but so important.

Thanks to all for pointing me in right direction
John

 

[rj51cxa]

Golom,

I missed your post whilst I was replying to the others. Thanks for your help.

Best Regards
John

 

[Golom]

You can do it that way but you are causing the system to evaluate every IIF statement even though it may have already found the answer. Effectively you are causing it to do (for example) the concatenation

"" & "" & "2003" & "" & "" & "" & "" & "" & ... etc.

 

[rj51cxa]

Golom,

I took another look at your "Right" expression as it is much more simple. However, when I try to run the expression, I get an error message saying "syntax error (comma)". I have gone through the expression a number of times but I cannot see where the comma problem is.

Would appreciate your help in sorting this out.

Best Regards
John

 

[PHV]

Why not simply this ?
Year: Right([BisleyYear] & '', 4)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Golom]

Sorry ... there was an extra "(" in there

IIF (    Right([BisleyYear],4) >= "2000" 
     AND Right([BisleyYear],4) <= "2010", 
     Right([BisleyYear],4), "")

That probably caused a parsing error and gave you the syntax message.

 

[rj51cxa]

Thanks Golom and PHV

Golom's corrected version was fine but PHV's is even more simple and will require no amending to suit the date range.

Thanks to both of you for your help.

Best Regards
John

 

[Golom]

Yes, PHV's solution is the simplest offered. What we don't know (and what may wreck both PHV's and my solutions) is that there may be strings in the table that don't conform to the pattern "SOME TEXT ... YEAR". If that does happen (or if "SOME TEXT" is different on some records and you don't want those records) then you will need to look at more elaborate solutions.

 

[rj51cxa]

Golom,

Thanks for the thought. As it turns out, in this table the format is the same throughout, but I will bear your comments in mind for the future.

Best Regards
John