Query "if" expression

[bks designs]

What is wrong with this:



Hubby: IIf([Husband's Name] Is Not Null,"(" & [Husband's Name] & ")"," ")

It works in other queries, but will not work in my new query. I have copied and pasted it even. I keep getting a messae when I go to data view: syntax error (comma) in query expression '[2015 2-6yers].IIf([Husband's Name] Is Not Null,"(" & [Husband's Name] & ")"," ").

This is for a yearbook - an if expression that if there is no husband listed, it is to be blank, but if there is a name it is to be inclosed in parentheses.

[June7]

Post the entire attempted sql statement.

Advise not to use spaces and special characters/punctuation (underscore is exception) in naming convention.

[bks designs]

Hubby: IIf([Husband's Name] Is Not Null,"(" & [Husband's Name] & ")"," ")

[June7]

That's not the entire SQL statement - that is just a single expression to calculate a field within a query. Post the entire query SQL statement as seen in SQL View of query builder. However, since I presume you are using the query builder, that likely won't help with analyzing the issue.

Really don't see anything wrong with the expression.

The query works without that expression?

Consider this alternative expression:

Hubby: "(" + [Husband's Name] + ")"

The + is valid concatenation character (holdover from ancient BASIC) but when the field is Null expression will return Null as opposed to & concatenation which will return ().

[bks designs]

Many thanks for looking at this. This expression has always worked before and was working in one of the queries but not in the other 2. As sometimes happens... I left the computer after working intensely for 5-6 hours, took a break, came back and now the queries are acting properly. I am making note of your suggestion!