Parameters in a query?

[Carol Geddes]

I have placed an expression column to concatenate the whole names of clients.



I used an IIF as the expression. I originally used " " to make a space and " and " to of course make the word and with a space on each side of it.

WholeNames: IIf([FName2] Is Not Null,IIf([ComLaw]=True,[FName1] & [ ] & [LName1] & [ ] & [“and”] & [FName2] & [ ] & [LName2],[FName1] & [“and”] & [FName2] & [ ] & [LName1]),[FName1] & [ ] & [LName1])

When I run the query I get a parameter dialog box asking me to enter the parameter value. It makes no difference if I have "" in there with no square brackets or whether I have it placed in the square brackets.
I realize that normally these kind of IIF's are better placed in a report or in word when the names are merged.
I would really like to use the expression field in Word rather than placing the code there.

Why does my query not recognise my & " " & ?
Thanks
Carol

[June7]

In IIf, don't use Is Not Null - use Not IsNull([FName2]). Don't use [ ] to define literal text (or space) - use " ".

WholeNames: IIf(Not IsNull([FName2]), IIf([ComLaw]=True, [FName1] & " " & [LName1] & " and " & [FName2] & " " & [LName2], [FName1] & " and " & [FName2] & " " & [LName1]), [FName1] & " " & [LName1])

[Carol Geddes]

Thank you for the Not IsNull
I will try that.

I have now replaced the [] with & " " & throughout my other expression column named WholeStreet.

WholeStreet: IIf([Unit#]=Null,[House#] & “ “ & [Street] & “ “ & [StType] & “ “ & [Quad],[Unit#] & “ “ & [House#] & “ “& [Street] & “ “ & [StType] & “ “ & [Quad])

Now when I run this query it says that I have Invalid Syntax.

It at least ran with the [] in there. Now it won't run at all.

??

Sorry that I have such a poor understanding of this.
Carol

[June7]

Can't use =Null, nothing is ever =Null. Review (see Error 5): http://allenbrowne.com/casu-12.html

That IIf syntax has an error but this is simpler:

WholeStreet: [Unit#] & IIf(IsNull([Unit#]), "", " ") & [House#] & " " & [Street] & " " & [StType] & " " [Quad]


BTW, advise no special characters or punctuation (underscore is exception) in names.

[Dal Jeanis]

By the way, if you're using IIF, and you're testing for Not IsNull(x), just use IsNull(x) and swap the order of the other two parameters. Easier to read.

[Carol Geddes]

Thank you all.
I see the error of my ways
Carol