IIF issue with Access in Office 10

[GregatUNCG]

Hi Everyone,



I'm wanting to yank my hair out. I am having an issue in Access (Office 10) that I never had in prior versions, and I am not a newbie with Access. In this version of Access, my IIF statements are not working. No matter what I do, I get "The expression you entered contains invalid syntax. You omitted an operand......"

The thing is if I do the function in any prior versions of Access it works fine. Additionally all the databases that I have from prior versions show the error in queries that have an IIF statement, but they WILL execute as long as I don't view the query.

This statement below is an example....it doesn't get much simpler than this.

FTPT: iff([TblA]#[RegHours]>5,"FT","PT")

Has anyone else had this problem in Office 10?

[orange]

I don't have Acc2010, but I don't understand what your expression means.

What is it in plain English? What is the "#"?

Also immediate if is IIF.

[GregatUNCG]

I should have just cut and pasted instead of relying on my fat fingers lol

the # of course should be !, and yep, I typed iff insted of iif


The statement is just an example, I have the error in all my IIF statements in Access 10.

[orange]

Try this Period not !
FTPT: iff([TblA].[RegHours]>5,"FT","PT")

[GregatUNCG]

I have tried the "." I've used the build gui, I've tried flat typing, I've tried cut and past from working queries in past versions. I've re-installed Office, loaded all the updates and patches. I'm at my wits end.

[orange]

Can you give/show a specific example? Full SQL for the query invovled.
Also, and I'm sure you've tried this, be sure that the field(s) in your iif(..) are all spelled correctly, and they really exist in your table(s).

I haven't seen posts suggesting a systematic error.

[GregatUNCG]

Orange, I have over 300 databases. This error occurs in every single one of them when I try to do it in Access 2010. Its not syntax. What adds to my frustration is that if I run a query that was created in a prior version of Access, the IIF processes correctly. If I look at the query in design mode, that error pops up and the query won't work again.

[orange]

As I said, I don't have Acc2010, and I haven't seen that specific issue reported elsewhere. It sounds like something specific to your install???
Does it happen with users of these databases? Or on all machines??

Not much help I'm afraid.

[GregatUNCG]

Only me thus far, but I do have a number of distributed apps and the users are running it fine, however they do not open the queries. I've re-registered acedao.dll, and that didn't help. Also as mentioned I reinstalled the entire Office package and that did nothing.

[GregatUNCG]

I just stumbled across the answer, and its one of those "omg you have got to be kidding me" issues. In the regional settings in the control panel, the list separator was not a comma. I've been dealing with this since February. Unbelievable.

[orange]

Glad you got it resolved.

[GregatUNCG]

Thanks for the help, Orange. Now I just need to talk to a Microsoft Engineer and ask what the HELL are they thinking!

[jrollins]

Try this Period not !
FTPT: iff([TblA].[RegHours]>5,"FT","PT")

When doing "IF" statements use IIF not iff (you still have two "ff's)... remember it's "Is IF" = "IIF"

[jrollins]

Removed as duplicated from my earlier reply

When doing "IF" statements use IIF not iff (you still have two "ff's)... remember it's "Is IF" = "IIF"

[peterr89]

This error occurs in every single one of them when I try to do it in Access 2010