ACC2000: Can't use Replace() in Control Source of report

[A Man]

Hi, I have a report in Access 2000. In a Control source of a text box I
have: =Trim(replace(UCase([LIST5]),"PER BOX","")).

Access does not recognize the replace function. Am I missing a reference
here? How do I fix this? LIST5 is my actual field and I want to remove
the string "PER BOX" from LIST5 if it exists.

Thanks.

 

[Ken Snell \(MVP\)]

Are you fully patched and updated for your 2000 ACCESS program? ACCESS 2000
did not recognize Replace function in queries and expressions until SP3, I
believe.

 

[Guest]

A work around would be to create a function to do the replace and make the
function the control source:

Private Function FormatControl(varOriginal As Variant) As String
FormatControl = Trim(replace(UCase(Nz([LIST5], vbNullString)), _
"PER BOX", vbNullString))
End Function

Note using Variant in the function is in case there is a null value in the
field LIST5]
Also, change of "" to vbNullString. Using an intrinsic constant rather than
a literal is a performance enhancer.

Since the Replace is in a function it should work.
--
Dave Hargis, Microsoft Access MVP

Are you fully patched and updated for your 2000 ACCESS program? ACCESS 2000
did not recognize Replace function in queries and expressions until SP3, I
believe.

--

Ken Snell
<MS ACCESS MVP>

Hi, I have a report in Access 2000. In a Control source of a text box I
have: =Trim(replace(UCase([LIST5]),"PER BOX","")).

Access does not recognize the replace function. Am I missing a reference
here? How do I fix this? LIST5 is my actual field and I want to remove
the string "PER BOX" from LIST5 if it exists.

Thanks.