My version of access does not like IIF.

[Mee]

Hello Everyone.
I am trying to insert the following field into a query: Units:IIF([RTCAKitNum]=665,"mg/mL", "ng/mL")
RTCAKitNum is a number field, the commas & quotes are correct but all I get is syntax error message. If I change it to
Test:IIF(1+1=2,"yes", "no")
I also get a syntax error. I can't find any test entry that will accept the syntax.
What am I missing?
Thanks for the help. Mee

 

[ypma]

Hello Everyone.
I am trying to insert the following field into a query: Units:IIF([RTCAKitNum]=665,"mg/mL", "ng/mL")
RTCAKitNum is a number field, the commas & quotes are correct but all I get is syntax error message. If I change it to
Test:IIF(1+1=2,"yes", "no")
I also get a syntax error. I can't find any test entry that will accept the syntax.
What am I missing?
Thanks for the help. Mee

Mee. I tested your Test:IIF(1+1=2,"yes", "no") on my versions of access which is 2016 and the syntax produces the correct result Yes .
What version are you using, maybe some of the forum members have the version you are using and can assist you further .
Have you tried creating a new query just using your Test syntax and no other fields apart from the ID ?

Ypma

 

[MajP]

Check to see if you have a broken reference. Do other string functions work (left, right, instr, etc)?

 

[Mee]

We are using Office 365 enterprise

-Mee

 

[arnelgp]

what is the mso language you are using?

 

[Mee]

what is the mso language you are using?

Ok, you have exceeded my understanding already. Is English a stupid answer?

 

[Mee]

Check to see if you have a broken reference. Do other string functions work (left, right, instr, etc)?

You're right, Left doesn't work either. Without extensive testing, I assume the others are broken as well.

 

[Minty]

I think @arnelgp was wondering what your international locale settings were.

In some locations the , gets replaced by a semicolon
IIf(1+1=2 ; "True" ; "False")

Edit - but I see @MajP has ht the nail on the head. References missing or a corrupt file.

 

[gemma-the-husky]

Perhaps "Units" is already defined as a field in the table.

When It's right IIF will only have the first letter capitalised - Iif

Maybe it's worth doing a compact and repair on your database because this should be working correctly.

 

[gemma-the-husky]

if left() isn't working, check your references. Open a code module, and do Tools/References. See if any are missing. Otherwise, maybe you need them in a different order.

 

[Minty]

@gemma-the-husky The iif will be displayed as IIf when the syntax is correct.
A sort of camel case for Immedite If

 

[arnelgp]

Ok, you have exceeded my understanding already. Is English a stupid answer?

sorry, because Other language uses semicolon instead of comma as separator.

 

[Mee]

@gemma-the-husky The iif will be displayed as IIf when the syntax is correct.
A sort of camel case for Immedite If

Interesting idea but not it. I tested IIF, Iif, IiF, if, IF, If.
One curious thing; when I switched from IIF to IF, I got the same syntax error. It didn't switch to "bad command" or some error like that.

I also tried swapping out the commas for ; . or |. Didn't like those either, although with the pipe, it did finally change the error message.

 

[MajP]

And all references are good?

 

[Mee]

And all references are good?

Yes. I've triple checked that.

 

[MajP]

These are kind of classic symptoms of the VBA library not loading due to a broken or missing reference, but if that is not the case you may have to do a reinstall. Can you post a screen shot of your references?

 

[Mee]

These are kind of classic symptoms of the VBA library not loading due to a broken or missing reference, but if that is not the case you may have to do a reinstall. Can you post a screen shot of your references?

It's a company computer so I'll have to get our IT to do it.

The RTCAKitNum field is coming from another query. The point of the query I'm trying to build is to join the query data with data from another table.

 

[Minty]

This has nothing to do with your query.

Open the VBA editor, press Ctrl + G to open the immediate window. Type the following;

? IIF(1+1=2,"yes", "no")

And hit return - you will almost certainly get an error.

To check your references in the same VBA editor click tools and select references;

What do you see? This is what I see O365 Win 10

 

[Mee]

I did as you suggested. ?IIF(1+1=... returned the correct answer, no errors here.
In the VBA editor, when I click tools, References is greyed out; I can't select it. Is this the source of all the problems and how do I fix it?

 

[Mee]

I take that back.
I see this

What does this mean?