Text replacement calculation returning error values

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
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","")
 
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.

Ofer Cohen said:
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


Can Of Worms said:
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?
Click to expand...
Click to expand...
 
Back
Top