syntax error in access when using iif in query expression

1
votes

I'm trying to create a Query based on an evaluation of an empty field.

Item: [Huis] & IIf(IsNull([Naam]), "", " / " & [Naam])  & IIf(IsNull([Druif]), "", " / " & [Druif]) & iif(IsNull([Type]), "", " / " & [Type])

It gives me a syntax error: Missing an operand or operator, you have entered an invalid character or comma or you have not enclosed the text in the expression in quotation marks.

I have tried other examples that are supposed to work here on stackoverflow, they all give the same error. What am I doing wrong?

1
ms-access
Can you post the SQL view of query. This seems to come from design view. Right now, I do not see comma or parentheses issues so it may be these table values if they exist or not. - Parfait
what version of access? try to use semi colon instead of comma in iif function. - M.Hassan
Access 2016, I indeed entered the expression in Design Mode, but I don't see why it could be wrong. Still have to find my way around a little, got me really demotivated. - Arne Clicteur
Using a semi colon also did the trick, thanks for your reply! - Arne Clicteur
In SQL view, the comma is always correct. But in Design view, with German and (apparently) French locale settings, semicolon is the function argument separator. - Andre

1 Answers

0
votes

I don't see an error either, but you can simplify your expression by taking advantage of the fact that & concatenates NULL values, but + doesn't.

Item: [Huis] & (" / " + [Naam]) & (" / " + [Druif]) & (" / " + [Type])

Each block, e.g. (" / " + [Naam]) will be NULL if the field is NULL.