Insert info memo field

[Hillbillie]

I am sending out letters to potential customers. I have their [company] fields in the report. Also the name of the memo field is [memo].

In the memo is a whole page full of text. At certain locations (more than one) is the characters [comp]

I want to replace all the [comp] within memo with the name of the company from the [company] field.

Thanks,

Larry

 

[Remou]

You could try the Replace function. It all sounds a bit unsafe, as 'comp' occurs in so mant words .

 

[Hillbillie]

Thanks, for the reply.

Actually there wont be any "[comp]" inside the memo except ones that we manually put in.

Can you give an example of using he replace function you mentioned above. Im not a programmer..
Thanks,

 

[Remou]

Add a textbox to your report and set its Control Source to a function:
=ReplaceComp([MemoFieldName])
Set Can Grow to Yes.

Add a function to the report module:

Function ReplaceComp(strText)
ReplaceComp = Replace(strText, "[comp]", [Company])
End Function

 

[Hillbillie]

I did as you wrote and when I ran the report it gave an error
"Compile error: External name not defined" and it highlighted the [Company] in the module.


I do have a text box called [Company]in the report I ran.

Also, in the new text box that I created in the report it reads "#Error"

and [comp] in the memo field is still not changed.

Thanks

 

[Remou]

Did you call your new textbox the same name as your memo field?
What happens if you type:
?replace("abc","b","a")
Into the immediate window?
Is company the name of a field in the table / query which is bound to the report?
Finally, if the questions above don't help, can you post your code?

 

[Hillbillie]

Did you call your new textbox the same name as your memo field? No


Is company the name of a field in the table / query which is bound to the report? Yes

What happens if you type:
?replace("abc","b","a") Sorry I need more simple instructions.
------------------------------------
I changed my module to this.

Function ReplaceComp(strText)
ReplaceComp = Replace(strText, "[comp]", Report_Report1![COMPANY])
End Function

Now it does insert the text into the text box but gives this error.

Invalid use of Null

---------------------
There is one thing I dont like about using a new text box. It doesnt have any formatting that my memo field has.

Cant I just replace the [comp] that is inside my memo?

I must keep my formatting in the memo.

Thanks

 

[Remou]

I think that you haven't put the function in the module belonging to the report as suggested, so you must change it to supply the company name.
In the report: =ReplaceComp([Memo],[Company])
Change the function to:

Function ReplaceComp(strText, strCompany)
If Trim(strText) & "" <> "" Then
    ReplaceComp = Replace(strText, "[comp]", strCompany)
End If
End Function

The If statement is for nulls.

I do not quite get 'formatting', most report controls only support a little formatting. However, you can use the Memo textbox, as long as you rename it.

If you wish to permanently change the memo field, you can do it with the function above and an Update query. In SQL view:

UPDATE tblTable SET tblTable.[MemoField] = ReplaceComp([MemoField],[Company])
WHERE ((Not (tblTable.MemoField) Is Null));

I think I have that right, but please test on a scratch table.