exp vg
asked on
Access Iif in syntax error
I keep getting an error on:
Food: IIF ([Event] In (“Orange”,“Apple Slice”,“Pineapple Slice”), “Fruit Group”)
Food: IIF ([Event] In (“Orange”,“Apple Slice”,“Pineapple Slice”), “Fruit Group”)
ASKER
Should I use single quotes instead of double?
and you need to add the false statement value of the IIF() function
the syntax is
IIf(expr, truepart, falsepart)
Food: IIF([Event] In ("Orange","Apple Slice","Pineapple Slice"), "Fruit Group", "False part value goes here")
the syntax is
IIf(expr, truepart, falsepart)
Food: IIF([Event] In ("Orange","Apple Slice","Pineapple Slice"), "Fruit Group", "False part value goes here")
ASKER
I am still getting an error message.
<Should I use single quotes instead of double? > it should be double quotes
the double quotes you used in your original post is the one used by MS Word and access will not understand it.
the double quotes you used in your original post is the one used by MS Word and access will not understand it.
upload a copy of your db.
What is the error message and #?
Seems strange that a field called [Event] would contain the name of a fruit or vegetable. Are you sure that is the correct field?
Seems strange that a field called [Event] would contain the name of a fruit or vegetable. Are you sure that is the correct field?
- incorrect field name is irrelevant for the syntax error, as long as the field name is in the table
even if you made a mistake of selecting the field from the table, it will not throw any error, it will just return blank result
.
even if you made a mistake of selecting the field from the table, it will not throw any error, it will just return blank result
.
Try adding Eval() around the first expression:
IIF( Eval([Event] In (“Orange”,“Apple Slice”,“Pineapple Slice”)), “Fruit Group”, "Veggie?")
https://support.office.com/en-au/article/IIf-Function-32436ecf-c629-48a3-9900-647539c764e3
IIF( Eval([Event] In (“Orange”,“Apple Slice”,“Pineapple Slice”)), “Fruit Group”, "Veggie?")
https://support.office.com/en-au/article/IIf-Function-32436ecf-c629-48a3-9900-647539c764e3
I'm not sure where you are going with this example. It seems to have been dumbed down to make it easy to explain. But, in the real world, you will almost certainly have more than one group and so a single IIf() isn't really going to solve the problem.
The best solution to give you flexibility and maintainability is to create a lookup table. Use a left join to the lookup table and pick up the group name if a record is found.
The best solution to give you flexibility and maintainability is to create a lookup table. Use a left join to the lookup table and pick up the group name if a record is found.
ASKER
The double quotes may be the issue. Let me retry.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you everyone - I have had not had the time today to revisit this - will do so as soon as I can. Appreciative of everyone's assistance.
ASKER
The quotes do seem to be the solution - when I type in Expression Builder I am able to save and do not get an error.
Is there another application I can use in substitute of the QBE since this is a small screen and the expression I will type will be lengthy.
Thank you.
Is there another application I can use in substitute of the QBE since this is a small screen and the expression I will type will be lengthy.
Thank you.
you can use Notepad or Notepad++
copy and paste this
Food: IIF([Event] In ("Orange","Apple Slice","Pineapple Slice"), "Fruit Group")