Access Query resulting in #Error for one outcome

Question

I am pulling information via a query in access. Below is my code. The results populate when the "Pinacle Type" is "DOM" or "BOOK", but if the type is anything else, as stated by the IIF statement, I get #Error.

5_BeneBankID: IIf([Pinacle_Type]="DOM" Or "BOOK",Mid(Replace(Replace([BeneABA]," ",""),"-",""),1,11),Mid(Replace(Replace([Intl_BeneBankID]," ",""),"-",""),1,11))

Embedded in this statement is are also formatting parts but those work for the first instance. The BeneABA field is a bank ABA number so this is always numeric. The Intl_BeneBankID is what is known as a SWIFT code which is either all alpha or alphanumeric. Both have a maximum length of 11 characters.

Also, If I type the following, the Intl_BeneBankID POPULATES! which is why I am stumped:

5_BeneBankID: Intl_BeneBankID

relevant table

Answer 1

I'm not sure, as your description of the problem seems vague to me, but if you are getting #error on some rows, but not all, you probably have null fields in your data. This is a common problem in test data, not so often in production code, but needs to be handled. Try wrapping the fields with Nz() i.e. ((Replace(Nz([Intl_BeneBankID],"")... and see what you get.