Replace Function returing #Error

[Ray67]

Hello-

I'm using this code CPT2new: Replace(Replace(Replace(Replace([CPT2],"+",""),"/",""),".",""),"*","") to remove characters.
When the fields are empty it returns #Error.

How can i get it to stop returning #Error?
If it's empty than i want it to return it empty.


Thank you

[ssanfu]

Not tested, but maybe use IIF()

CPT2new: IIF(Len(Trim([CPT2]))=0,"", Replace(Replace(Replace(Replace([CPT2],"+",""),"/",""),".",""),"*",""))

[Ray67]

Steve

I try it but still getting #Error

Not tested, but maybe use IIF()

CPT2new: IIF(Len(Trim([CPT2]))=0,"", Replace(Replace(Replace(Replace([CPT2],"+",""),"/",""),".",""),"*",""))

[JoeM]

This works:

CPT2new: Replace(Replace(Replace(Replace(NZ([CPT2],""),"+",""),"/",""),".",""),"*","")

[Ray67]

Thank you very much Joe
Worked Great!!!!!!!!!!!!!!

This works:

CPT2new: Replace(Replace(Replace(Replace(NZ([CPT2],""),"+",""),"/",""),".",""),"*","")

[JoeM]

Your welcome!

[ssanfu]

Well, I learned something...

I have used (in A2000)
[code[Len(Trim([FieldOrControlName]))=0 [/code]

to determine if it is non-null and non-space(s) because just using NZ() was allowing some records to be calculated when they shouldn't be.

Apparently A2010, won't allow just spaces to be entered into a field.

In the query, even using

CPT2newLT: Len(Trim([CPT2]))=0

where CPT2 is NULL, doesn't result in TRUE, it returns NULL.

Trying

CPT2newL: Len([CPT2])

when CPT2 is NULL, returns NULL, not 0...

It looks like I have a lot of code to review...


BTW, in testing I found this also works:

CPT2new: Replace(Replace(Replace(Replace([CPT2] & "","+",""),"/",""),".",""),"*","")