formula too complex (1 Viewer)

[sammers101]

I'm getting the error: expression is typed incorrectly or it is too complex

Basically I want to truncate before either a " #", " annual", or " tp"

series1: Left([comic],IIf(InStr(1,[comic]," #")=0,Len([comic])+1,InStr(1,[comic]," #"))-1)

series2: Left([series1],IIf(InStr(1,[series1]," annual")=0,Len([series1])+1,InStr(1,[series1]," annual"))-1)

series3: Left([series2],IIf(InStr(1,[series2]," tp")=0,Len([series2])+1,InStr(1,[series2]," tp"))-1)

 

[JHB]

It think the problem is you want to select something that is longer as it is, take away the +1.
Len([series1])+1

 

[arnelgp]

wrap in a function.
put in a module:

public function SeriesSubString(vField As Variant, sStringToFind As String) As String
dim lngPos As Long
vField = vField & ""
' return the value right away
SeriesSubString = vField
lngPos = Instr(vField, sStringToFind)
If lngPos <> 0 then SeriesSubString = Left(vField, lnPos-1)
End Function

to call the function:

series1: SeriesSubString([comic]," #")

series2: SeriesSubString([series1]," annual")

series3: SeriesSubString([series2]," tp")

 

[sammers101]

I got it to work by removing the totals. Thanks for the help