Need with help with Iif statement syntax to show field value over another
Need help with this syntax for a complex Iif statement in a Microsoft Access query.
The following is taken from the Contacts MS Access database in a query.
Contact Name: IIf(IsNull([LastName]),IIf
Obviously if the First and Last Name fields are not blank then it goes and makes Contact Name's value those two fields combined.
I'm happy with that - however I have another contingency where I want to override and only show PreferredName field to show as a priority over first and last name.
How do I change the above Iif statement to include that contingency?
Again, don't want another solution - just want the updated syntax to include PreferredName field and not first and last name if it is not blank. If PreferredName field is blank then use both First and Last Name fields as stated above.
Thank you in advance for your help with this question! Also not looking for another similar type of solution. Only getting the correct syntax using Iif statement only.
The following is taken from the Contacts MS Access database in a query.
Contact Name: IIf(IsNull([LastName]),IIf
Obviously if the First and Last Name fields are not blank then it goes and makes Contact Name's value those two fields combined.
I'm happy with that - however I have another contingency where I want to override and only show PreferredName field to show as a priority over first and last name.
How do I change the above Iif statement to include that contingency?
Again, don't want another solution - just want the updated syntax to include PreferredName field and not first and last name if it is not blank. If PreferredName field is blank then use both First and Last Name fields as stated above.
Thank you in advance for your help with this question! Also not looking for another similar type of solution. Only getting the correct syntax using Iif statement only.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Appreciate all the replies.
Paul, I tried your Iif statement several times and it kept coming up with an error. I tried adding and subtracting parenthesis but nothing worked.
Dale, i wa able to copy and paste and it worked like a charm. Just curious but does the Switch statement work in older versions of Microsoft Access? I'm in a situation where people have multiple copies of Access and I hope that won't throw an error with older copies.
Paul, I tried your Iif statement several times and it kept coming up with an error. I tried adding and subtracting parenthesis but nothing worked.
Dale, i wa able to copy and paste and it worked like a charm. Just curious but does the Switch statement work in older versions of Microsoft Access? I'm in a situation where people have multiple copies of Access and I hope that won't throw an error with older copies.
Glad I could help.
I frequently prefer to put this type of logic in a function that I can call from the query, as nested iif's are difficult to read. Switch is easier to read, but can still result in a long string, while a function allows you to add comments so people understand why you used the logic you did.
Switch works at least as far back as 2003, not sure about older than that.
Dale
I frequently prefer to put this type of logic in a function that I can call from the query, as nested iif's are difficult to read. Switch is easier to read, but can still result in a long string, while a function allows you to add comments so people understand why you used the logic you did.
Switch works at least as far back as 2003, not sure about older than that.
Dale
So create a new IIF statement, with your original IIF statement as the FALSE portion:
IIf(Not IsNull([PreferredName], [PreferredName], {FALSE} )
IIf(not IsNull([PreferredName], [PreferredName], ( IIf(IsNull([LastName]),IIf