Query expr causing the expression you entered has a function with the wrong number of arguments

[Ansiep1]

Hi
I have to run a query to concatenate 3 surnames. Surname1 and Surname2 can be the same. Surname3 may be blank

I am using the following expression:

Surnames: IIf([Surname2]=[Surname1],[Surname1],[Surname1] & " and " & [Surname2] & " and " & [Surname3],IIF(IsNull([Surname3]),[Surname1] & " and " & [Surname2],[Surname1] & " and " & [Surname2] & " and " & [Surname3]))


I receive the following error message: the expression you entered has a function with the wrong number of arguments" How do I correct this expression?

[Welshgasman]

IIF() function needs True and False arguments.
I would tend to work from the inside out, if I was to create such a convoluted statement.

I would most likely create a function and do it bit by bit.

[davegri]

Your second iif needs to be one of the first iff's arguments; it can't stand alone like that.
An example of the names and what you expected as the outcome would have been helpful, but with assumptions of your expectations on my part, try this:

Surnames:IIf([surname2] = [surname1], [surname1], [surname1] & " and " & [surname2] & IIf(Not IsNull([surname3]), " and " & [surname3], ""))

Edit: simpler and more accurate:

Surnames: surname1 & IIf([surname2] = [surname1], "", " and " & surname2) & IIf(Not IsNull(surname3), " and " & surname3, "")

Edit: Third attempt, works better

Surnames: surname1 & IIf([surname2] = [surname1], "", IIf(IsNull(surname2), "", " and " & surname2) & IIf(Not IsNull(surname3), " and " & surname3, ""))

And here's a function that works and only took 5 minutes to write and debug (Don't you just hate those compound IIFs ?)

Public Function fcnSurnames(n1 As String, n2 As String, n3 As String)
    Dim rslt As String
    rslt = n1
    Select Case (n2 & "") = ""
        Case False
            Select Case n1 = n2
                Case False
                    rslt = rslt & " and " & n2
            End Select
    End Select
    Select Case (n3 & "") = ""
        Case False
            rslt = rslt & " and " & n3
    End Select
    fcnSurnames = rslt
End Function

None of these account for Surname 1 being blank
OR surname1 = surname3
OR surname2 = surname3

[Minty]

If I'm not mistaken would this be be even simpler ?

Surnames: Surname1 & IIf([surname2] <> [surname1], "", (" and " + [surname2]) & (" and " + [surname3]))

[davegri]

@Minty
Use of + is clever to account for nulls.
However, the <> should be = in your expression.

The OP did specify Nulls, but the function I posted (as of late) in post#3 will handle both nulls and zls.

[Minty]

@Minty
Use of + is clever to account for nulls.
However, the <> should be = in your expression.

The OP did specify Nulls, but the function I posted (as of late) in post#3 will handle both nulls and zls.

I originally coded it the other way around hence <> , but when I looked at it, it looked weird/awkward so I swapped the true/false expressions around, only to forget to change it to =.
Ooops!