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

This is what I see:

Answer 2

Press the Windows key+R.

Type

control international

then press Enter or click OK. This opens the Region control panel.

Click the 'Additional settins...' button.

What are your settings for 'Decimal symbol', 'Digit grouping symbol' and 'List separator'?

This is what I see:

Answer 3

Thank you for your reply and for offering to help. You are right, I made up a simple iif example to illustrate the problem. Below I will give details regarding what I'm trying to do.

I have a file with addres info. Some of the zip code fields lack a leading '0' so I want to add the leading zero in my query. I created a column to capture the length of the zip (Fized Res Zip) and want to use an update query to add the zero when the length of Residential Address Zip is either 4 or 8 and capture the updated zip in Fixed Res Zip2. Here's the data:

And here's the query:

And this is the error I get when I try to run the query:

Here are my region settings:

And when I start typing the function, the below pops up making me think that perhaps pipe delimiters were now used. I tried them as well as semicolons and none work.

I imagine this is something really simple that I'm missing but can't figure it out so I appreciate your help and please let me know if you need any more info.

Answer 4

Does your code compile? Does this also happen in a new, blank Access file?

Answer 5

Product support is aimed at a different level of problem. It's indeed frustrating, but specific usage, logic and design questions aren't in the skill set of the Microsoft employees working their support service. For that, you need experienced, working Access developers who have day to day hands on experience, as you find here.

There are many other forums, as well, but it's understandable that Microsoft would first direct you to this one they support directly.

Have patience, provide us the details we need to help you diagnose and understand your problem, and the people here will do their best to help you get to a resolution.