Error using iif function

Question

When using the iif function I get an error that says 'you omitted an operand or operator, you entered an invalid character or comma, or you entered text without surrounding it in quotation marks'. I get this even when I try:

iif((1+1=2),"yes","no")

I've been working on this for a few hours, trying different things. I called MS support and apparently this is the only forum to get help. Having that function for the work I'm doing is critical. Any suggestions please?

Answer 1

Hi, yes, I initially tried to add the zero without the helper field but the iif didn't work. So I created the helper field for a couple of reasons - to confirm a function worked at all (it did, the LEN function), and also thinking that perhaps for some reason the embedded function was the problem (though it never was before). When it didn't work I created the very simple iif test (iif(1+1=2,"yes","no") and that didn't work either. It was then that I poked around on this site, called MS, then was referred back to this site. It seems it's the iif function that's having a problem.

Answer 2

Okay, I'm still trying to set up some testing on the original syntax, but this expression should handle ZIP codes with lengths of 4 characters, 5 characters, 8 characters or 9 characters and return either the original 5 or 9 character ZIP, or prepend the leading 0 for 4 and 8 character ZIPS. There is no need for the interim "helper" field that calculates the length separately.

Iif(Len([Residential Address Zip]) =4 Or Len([Residential Address Zip]) = 8, Right("0" & [Residential Address Zip], 9) ,[Residential Address Zip])

Working examples:

Answer 3

I think that the regional settings and delimiters is a red herring, based on what we can actually see in the screenshot and discussions.

First, the error message mentions 3 different possible reasons for the error:

• Omitted (left out) an operand or operator
• An invalid character or comma
• Text (i.e. alpha characters) not surrounded with quotes

Any one of those three, and some minor variations among them perhaps, could be the problem.

However, in trying to work out an example to address the problem, I got the same error. I'm not convinced yet that this is not a bug. I need to dig further into it to see if I can find a reason for this.

Answer 4

I 've found the list separator is | rather than comma (,).

so,maybe

iif(1=1|"yes"|"no")

Answer 5

Not sure what you mean when askiing if my code compiles.

This error happens in a new DB.