Text replacement calculation returning error values

[Can Of Worms]

CaseNo:
IIf(Right([CaseList]![CaseNo],3)="RES",Left([CaseList]![CaseNo],Len([CaseList]![CaseNo]-3)),[CaseList]![CaseNo])

The above is a the calculation that is run on a query, in which I want to
look for values in the 'CaseNo' field which have an "RES" suffixed on the end
of the case number, and strip it back off if so. The above caluclation does
do that. However I have a string of values in the CaseNo field which begin
with "S", and the above caluclation returns '#error' as the value.

The CaseNo values are all text type.

Unfortunately, the CaseNo values are from 3 to 5 characters in length, and
some of those have the "RES" on the end, and I can't rule out that I won't at
some point get 6 character CaseNo values, with and without the "RES". So I
can't go by just the length of the string to determine what to return.

Anyone have any insight into why the #error values are being returned?

 

[Ofer Cohen]

If you want to remove the RES from the string you can use

Replace([CaseList]![CaseNo],"RES","")

If you want to remove the S that left on the right, then you can use

Replace(Replace([CaseList]![CaseNo],"RES",""),"S","")

 

[Can Of Worms]

Thanks! This works perfectly.

I knew this function was available in Excel, but I was not coming across it
in help files, and it does not appear in the Build In Functions list in the
expression builder.

If you want to remove the RES from the string you can use

Replace([CaseList]![CaseNo],"RES","")

If you want to remove the S that left on the right, then you can use

Replace(Replace([CaseList]![CaseNo],"RES",""),"S","")


--
Good Luck
BS"D

CaseNo:
IIf(Right([CaseList]![CaseNo],3)="RES",Left([CaseList]![CaseNo],Len([CaseList]![CaseNo]-3)),[CaseList]![CaseNo])

The above is a the calculation that is run on a query, in which I want to
look for values in the 'CaseNo' field which have an "RES" suffixed on the end
of the case number, and strip it back off if so. The above caluclation does
do that. However I have a string of values in the CaseNo field which begin
with "S", and the above caluclation returns '#error' as the value.

The CaseNo values are all text type.

Unfortunately, the CaseNo values are from 3 to 5 characters in length, and
some of those have the "RES" on the end, and I can't rule out that I won't at
some point get 6 character CaseNo values, with and without the "RES". So I
can't go by just the length of the string to determine what to return.

Anyone have any insight into why the #error values are being returned?