filtering out "#Error"

[christianlott1]

I'm doing a replace function on a phone number field:

Expr1: NZ(ReplaceText([PROV_PHONE]),"")

and it converts most of them but for others I get this "#Error" in the
fields. Since I will need to do a join on this field to another table,
I'm confident Access will spit out an error when/if I do.

I tried handling it with the 'NZ' as you see above and also in the
function itself:

Public Function replaceText(inText As String) As String
On Error GoTo Errer
Dim reText As String

reText = Replace(inText, " ", "")
replaceText = Replace(reText, "-", "")
Exit Function

Errer:
replaceText = ""
End Function

[Allen Browne]

Strings cannot be null.
Change the ReplaceText() function so it can accept a variant.
You probably want it to be able to return a null as well.

Public Function replaceText(inText As Variant) As Variant
On Error GoTo ErrHandler

replaceText = Null
If Not IsNull(inText) Then
replaceText = Replace(Replace(inText, " ", ""), "-", "")
End If

ExitHandler:
Exit Function

ErrHandler:
resume ErrHandler
End Function

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<ch************@yahoo.comwrote in message
news:11**********************@k78g2000cwa.googlegr oups.com...

I'm doing a replace function on a phone number field:

Expr1: NZ(ReplaceText([PROV_PHONE]),"")

and it converts most of them but for others I get this "#Error" in the
fields. Since I will need to do a join on this field to another table,
I'm confident Access will spit out an error when/if I do.

I tried handling it with the 'NZ' as you see above and also in the
function itself:

Public Function replaceText(inText As String) As String
On Error GoTo Errer
Dim reText As String

reText = Replace(inText, " ", "")
replaceText = Replace(reText, "-", "")
Exit Function

Errer:
replaceText = ""
End Function

[christianlott1]

Of course, that worked perfectly.

Strange thing though.. I filtered on the result of the replace in the
designer - 'Not Null' and then 'Not 0' and it returned an infinite
record set.