Forum Discussion
Access adds brackets to text in quotes in Iif statement
I have a query of 1 short text field. If the field has the value "NOCOV" I want to return Null, otherwise I want the value in the short text field. The problem is that Access add brackets around "NOCOV" and treats it like a parameter.
I enter:
PRM_PreMedicare_Plan_ID: IIf([PRM Pre-Medicare Plan ID]=“NOCOV”,Null,[PRM Pre-Medicare Plan ID])
The result is:
PRM_PreMedicare_Plan_ID: IIf([PRM Pre-Medicare Plan ID]=[“NOCOV”],Null,[PRM Pre-Medicare Plan ID])
It doesn't matter if I use <> or Like instead of = or Switch instead of IIf. The same problem occurs. I don't know how to prevent Access from adding brackets.
I'm using Access for Office 365 Version 2603.
2 Replies
I suspect you've fallen prey to using a Lookup field for the field named [PRM Pre-Medicare Plan ID]
Presumably it has a suffix of ID because it stores the foreign key for the Plan.
The ID for that related Pre-Medicare Plan is not a text string, it is the numeric value. In other words, despite that fact Lookup fields SHOW strings like "NOCOV", they actually store a number.
Therefore, when you try to use the string value displayed in that Lookup field, Access isn't able to resolve what the heck you mean and assumes you are trying to reference something else.
The better solution is to rip out Lookup fields in tables and explicitly store ONLY the relevant Foreign Key value for the ID. That disambiguates a lot of things, such as this IIf() expression.
A work-around would be to use the actual number for the "NOCOV" record's ID, not the display text.
I may be wrong, of course, but this scenario, along with the tell-tale "ID" in the field name suggests it is a likely reason for the errorYou are probably using special (typographic) quotation marks. Try using simple quotes.
