Expression has wrong number of arguments

[tinareed]

Please help out a newbie. I've copied a complicated query and am trying to modify it for mailing labels. First, last name and primary address are the fields. Primary address would be either a home or work address. The prior query was made to list primary phone number of which the number was just one field (ex: 213-300-0000). However, the primary address would include street address, city, state and zip for a total of 4 fields. I receive an error after I add the city, state and zip fields after the street address field in the expression.

Error: The expression you entered has a function containing the wrong number of arguments.

Here is the code I am using:
Primary Address: IIf([txtPrimaryAddr]='home',[txtAddress],[txtCity],[txtState],[txtZip],IIf([txtPrimaryAddr]='business',[txtWorkAddr],[txtWorkCity],[txtWorkState],[txtWorkZip])

Any advice?

Thanks in advance.

[akirekab]

Please help out a newbie. I've copied a complicated query and am trying to modify it for mailing labels. First, last name and primary address are the fields. Primary address would be either a home or work address. The prior query was made to list primary phone number of which the number was just one field (ex: 213-300-0000). However, the primary address would include street address, city, state and zip for a total of 4 fields. I receive an error after I add the city, state and zip fields after the street address field in the expression.

Error: The expression you entered has a function containing the wrong number of arguments.

Here is the code I am using:
Primary Address: IIf([txtPrimaryAddr]='home',
[txtAddress],[txtCity],[txtState],[txtZip],IIf([txtPrimaryAddr]='business',[txtWorkAddr],[txtWorkCity],[txtWorkState],[txtWorkZip])

Any advice?

Thanks in advance.

I am not an expert, but First I would avoid using IIF in VBA, as per a note elsewhere in this forum. I am answereing since I dont see anyone else trying this one.
That being said, IIf takes and expression and if true, returns the first argument, after the expression, and if false returns the second argument. Since a comma "," is an integral part of the function, I am thinking you can only have two. Like this:

IIf Function Example
This example uses the IIf function to evaluate the TestMe parameter of the CheckIt procedure and returns the word "Large" if the amount is greater than 1000; otherwise, it returns the word "Small".

Function CheckIt (TestMe As Integer)
CheckIt = IIf(TestMe > 1000, "Large", "Small")
End Function

I think I would use a plain If, then,else statement to resolve your issue.
Like:
[code]
If "txtPrimary Addr = Home then
[txtAddress],[txtCity],[txtState],[txtZip]
else if ([txtPrimaryAddr]='business') then
[txtAddress],[txtCity],[txtState],[txtZip]
End If
{/CODE]

My syntax my be a little off, and I am not sure how your differentiating between what txtAddress is home and business, but hopefully some of this is helpful.

[FishVal]

IIf() function is expected to have 3 arguments.
Fields [txtAddress],[txtCity],[txtState],[txtZip] separated by commas are recognised as 4 arguments.
Iif you want to merge them use concatenation operator "&".

Regards,
Fish

[tinareed]

It worked, Fish, thank you both so much for answering...Now on to formatting the mailing labels.

Best,
TR

[tinareed]

Another question related to this query:

I used the mailing label wizard for the report using this query.
1. The first and last name appear correctly on the first line of the label. However, there is no space between the 2 fields.
2. The primary address field consists of street address, city, state and zip. They all show up on the 2nd and 3rd lines of the labels with no line or space breaks.

How can I correct these formatting problems? Is there code I can insert in the query expression? Thanks a lot, I've looked in my manuals and do not see this situation where the address is combined into one field because of the primary address expression.

[FishVal]

Concatenate the fields with spaces and/ or separators.
fld1 & " " & fld2 & ", " & fld3

Regards,
Fish

[tinareed]

I found the answer to my questions. I was looking in the wrong place. Thanks for considering this if you had read it. :-)