IIF Error

[Michael J Ross]

Im currently trying to create a field that strips plc and ltd suffixes from vendor names, and if theyre not there just return the name. I've tried the following which works fine for those records where plc or ltd exists in the name, but where it doesnt it's returning error.

SELECT IIf(InStr([organization_operating_name],"ltd")>0,Replace([organization_operating_name],"ltd",""),IIf(InStr([organization_operating_name],"plc">0,Replace([organization_operating_name],"plc","")),[organization_operating_name])) AS OrgName, VendorsPdinLastYr.vendor_id
FROM VendorsPdinLastYr;

Can anyone point where i'm going wrong?

Thanks

 

[Brianwarnock]

If that's a copy and paste of the code then you need ) after "plc", but don't know why that wouldn't be flagged

Brian

 

[Michael J Ross]

Thanks for that Brian, done that and rejigged the brackets and its now working. Code is

SELECT IIf(InStr([organization_operating_name],"ltd")>0,Replace([organization_operating_name],"ltd",""),IIf(InStr([organization_operating_name],"plc")>0,Replace([organization_operating_name],"plc",""),[organization_operating_name]))AS OrgName, VendorsPdinLastYr.vendor_id
FROM VendorsPdinLastYr;