Problem using 'Replace' to trim string 1

[IamreallyStuck]

Hello,

I have a module that strips square brackets from any strings which may contain them. It works fine, unless the string passed into the function is empty, in which case the function returns '#Error'. I've tried to rewrite it to stop this from happening, but i'm new to Access and VBA so I can't get the function to handle empty strings.
This is my function at the moment:

Option Explicit

Function SwapStuff(myStr As String) As String
Dim strHolder As String
If Len(myStr) > 0 Then
strHolder = Replace(myStr, "[", "&quot
SwapStuff = Replace(strHolder, "]", "&quot
Else: SwapStuff = ""
End If
End Function

I'm sure that it won't take much to fix this - if anyone could help i'd really appreciate it.
Thanks,
Lee.

 

[DrSimon]

It's often very awkward to deal with 'empty' and 'null' strings - the problem is that it is difficult to know which actually applies. You will probably need to insert:

If not isempty(myStr) and not isnull(myStr) then
if len(myStr) > 0 Then
....
end if
end if

Simon Rouse

 

[IamreallyStuck]

Thanks for your reply Simon,
My module now looks like this:

Option Explicit

Function SwapStuff(myStr As String) As String
Dim strHolder As String

If Not IsEmpty(myStr) And Not IsNull(myStr) Then
If Len(myStr) > 0 Then
strHolder = Replace(myStr, "[", "&quot
SwapStuff = Replace(strHolder, "]", "&quot
End If
End If

End Function

Unfortunately the empty fields are still being filled with '#Error'.

Lee.

 

[DrSimon]

You don't say how/where you are applying this function. Is it in a form/report or in some VBA code which is updating records in a table?

 

[IamreallyStuck]

I am using this in a query field:

BracketsRemoved: SwapStuff([BracketString])

The problem is that sometimes BracketString is empty, sometimes not.

Lee : )

 

[DrSimon]

It's not the function that's wrong - it's the call from the query. Get rid of the code I suggested - it's not needed. What you need is:

BracketsRemoved: IIf(IsNull([BracketString]),"",SwapStuff([BracketString]))

Simon Rouse

 

[IamreallyStuck]

Thank you for your help Simon,
The query is working fine now : )
Lee.