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

  • Thread starter Thread starter A Man
  • Start date Start date
A

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.
 
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.
 
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


Ken Snell (MVP) said:
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>


A Man said:
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.
Click to expand...
Click to expand...
 
Back
Top