#Func! Error I can’t get around

Question

I have a column of data ([Premium Hdr].[Rev]) that contains blanks, letters and strings containing letters+numbers. I need to do a Nz on the blanks, trim the + and numbers if they are there, and if there are just letters, return that in a calculated column.

Column:

blank

A

B+1

C+23

QQ

CCB+2

My statement handles the Nz stuff fine, handles the + trim fine, but returns a #func! if there isn’t a + for the statement to work against.

IIF(IsNull([Prem Hdr].[Rev]),””,IIf(IsError(Left(Nz([Prem Hdr].[Rev],””),InStr(Nz([Prem Hdr].[Rev],””),”+”)-1)),[Prem Hdr].[Rev],Left(Nz(Prem Hdr].[Rev],””),InStr(Nz([Prem Hdr].[Rev],””),”+”)-1)))

Answer 1

Changing the iserror to an InStr = 0 allows for an initial value without a + to be handled correctly