using large amounts of text in reports

[robertm600635]

What I need to do is make a report that has a few paragraphs of text with a
data field inserted in certain places here and there. All I can come up with
is either use labels or make a field in a table and store the text there and
then put the field on the report. Neither of these solutions seem very good
to me. any ideas of a good way to do this?

 

[Allen Browne]

Use a memo field to store the text, with a special way of indicating the
value to be inserted. Then use the Replace() function to replace the text
with the value of the field.

Say:
- Your Customer table has a text field named FirstName
- Your Letter table has a memo field named TheLetter.
- You use braces around the field name as the way to indicate the value to
be inserted, so the memo field might contain this:
Thank you, {FirstName}, for your donation.

As the source for your report, you create a query containing both tables, so
both fields are in the report. Now in the report, your text box has its
Control Source set to:
=Replace([TheLetter], "{FirstName}", [FirstName])
When you run the report, the Replace() function finds all instances of the
literal text in the memo field, and replaces it with the value of the
FirstName field.

If you have several of these fields to substitute, you probably want to
write a function that uses Replace() for each one to drop in the right text.

 

[bhipwell via AccessMonster.com]

I am not clear what you are trying to do. Sounds like you have a fixed body
of text that will have some words within it that will be variable? Is this
correct? If so, you can just use a text box to build the paragraph leaving
spacing to place fields within/over it setting the fields with transparent
backgrounds and no border. But I think I need to know more bout what you are
trying to accomplish.

B

 

[robertm600635]

You are basically correct about what I'm trying to do, and your solution
sounds good. My report needs to looks something like:

I hereby state that [ClientName] will do the following, more text....

where client name is the field. Also, I need to just insert the [ClientName]
for the current selected record when I print off this report. I thought maybe
this could be easily accomplished by merging the data into MS Word, but have
found that not to work to well. Any suggestions?

 

[robertm600635]

Also, there's is about 8 pages of text that i need in this report, I haven't
figured out how I'm going to get all that in there, which is why a Word doc
seemed like the way to go.

 

[Allen Browne]

This example assumes a table named tblReplace, with fields named FindWhat
and Replacement. Sample records:
{First Name} [FirstName]
{City} [Suburb]
i.e. the first column is the literal text to be replaced, and the 2nd column
is the field name.

Your textbox is bound to:
=ReplaceThemAll([NameOfYourMemo])


Function ReplaceThemAll(ByVal strSource As String)
Dim rs As DAO.Recordset

Set rs = DBEngine(0)(0).OpenRecordset("tblReplace", dbOpenDynaset)
Do While Not rs.EOF
strSource = Replace(strSource, rs!FindWhat, rs!Replacement)
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
ReplaceThemAll = strSource
End Function

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen,

Do you have an example of the funtion to uses replace() to replace several
different values in the text?

Mr B

Use a memo field to store the text, with a special way of indicating the
value to be inserted. Then use the Replace() function to replace the text
with the value of the field.

Say:
- Your Customer table has a text field named FirstName
- Your Letter table has a memo field named TheLetter.
- You use braces around the field name as the way to indicate the value
to
be inserted, so the memo field might contain this:
Thank you, {FirstName}, for your donation.

As the source for your report, you create a query containing both tables,
so
both fields are in the report. Now in the report, your text box has its
Control Source set to:
=Replace([TheLetter], "{FirstName}", [FirstName])
When you run the report, the Replace() function finds all instances of
the
literal text in the memo field, and replaces it with the value of the
FirstName field.

If you have several of these fields to substitute, you probably want to
write a function that uses Replace() for each one to drop in the right
text.

message

What I need to do is make a report that has a few paragraphs of text
with
a
data field inserted in certain places here and there. All I can come up
with
is either use labels or make a field in a table and store the text
there
and
then put the field on the report. Neither of these solutions seem very
good
to me. any ideas of a good way to do this?

 

[Mr B]

Allen,

I have tried and tried to implement the replacement function. The function
is executing. However I am getting the field name as defined in the
tblReplace table. For example, I literally get "[FirstName]" instead of
getting the value being returned by the record source.

I am using an sql statement as the record source for the report. I have
criteria in this statement the will return one record. I cannot seem to get
the fields to translate to the values.

Your help is appreciated.

Mr B

This example assumes a table named tblReplace, with fields named FindWhat
and Replacement. Sample records:
{First Name} [FirstName]
{City} [Suburb]
i.e. the first column is the literal text to be replaced, and the 2nd column
is the field name.

Your textbox is bound to:
=ReplaceThemAll([NameOfYourMemo])


Function ReplaceThemAll(ByVal strSource As String)
Dim rs As DAO.Recordset

Set rs = DBEngine(0)(0).OpenRecordset("tblReplace", dbOpenDynaset)
Do While Not rs.EOF
strSource = Replace(strSource, rs!FindWhat, rs!Replacement)
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
ReplaceThemAll = strSource
End Function

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen,

Do you have an example of the funtion to uses replace() to replace several
different values in the text?

Mr B

Use a memo field to store the text, with a special way of indicating the
value to be inserted. Then use the Replace() function to replace the text
with the value of the field.

Say:
- Your Customer table has a text field named FirstName
- Your Letter table has a memo field named TheLetter.
- You use braces around the field name as the way to indicate the value
to
be inserted, so the memo field might contain this:
Thank you, {FirstName}, for your donation.

As the source for your report, you create a query containing both tables,
so
both fields are in the report. Now in the report, your text box has its
Control Source set to:
=Replace([TheLetter], "{FirstName}", [FirstName])
When you run the report, the Replace() function finds all instances of
the
literal text in the memo field, and replaces it with the value of the
FirstName field.

If you have several of these fields to substitute, you probably want to
write a function that uses Replace() for each one to drop in the right
text.

message
What I need to do is make a report that has a few paragraphs of text
with
a
data field inserted in certain places here and there. All I can come up
with
is either use labels or make a field in a table and store the text
there
and
then put the field on the report. Neither of these solutions seem very
good
to me. any ideas of a good way to do this?

 

[Allen Browne]

Mr B, you are correct.

My apology: I found an old function I wrote years ago, and pasted it without
checking. It does a literal replace, not a merge as you requested.

I will look at this further, and get back to you.

 

[Allen Browne]

There are some rather inefficient solutions to this, using DLookup() on each
possible combination.

What I actually did was do this at the time the memo field is entered,
rather than at the time the report is run. So, you have a boilerplate
template of the paragrah(s) you wish to combine for this "letter", and you
replace the {fieldname} in the bolierplate with the value of the field in
the recordset at the time you assign the value to the memo field. That has 2
advantages: a) the user can actually change the text of the letter to
anything they want, and the b) the inefficient stuff runs quickly when you
create the record, instead of running slowly for many records when you try
to generate the report.

Is that any use for what you need?

If you don't need the flexibility of these lookup tables and editable
replacements, you could just concatenate the text directly in the Control
Source of the text box to:
"Thank you for you correspondence dated " & Format([LetterDate], "Long
Date") & " referring to " & [LetterTopic] & "." & Chr(13) & Chr(10) & "You
can expect a call from our " & [StaffTitle] & " " & [StaffFirstName] & " " &
[StaffSurname] & " within the next " & [ResponseDays] & "."

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Mr B, you are correct.

My apology: I found an old function I wrote years ago, and pasted it
without checking. It does a literal replace, not a merge as you requested.

I will look at this further, and get back to you.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen,

I have tried and tried to implement the replacement function. The
function
is executing. However I am getting the field name as defined in the
tblReplace table. For example, I literally get "[FirstName]" instead of
getting the value being returned by the record source.

I am using an sql statement as the record source for the report. I have
criteria in this statement the will return one record. I cannot seem to
get
the fields to translate to the values.

Your help is appreciated.

 

[Allen Browne]

If all the replacements were actual field names, another option would be to
use an unbound text box, and write its value in the Format event of the
(Detail?) section of the report.

In a loop, use Instr() to locate the opening brace and matching closing
brace, and build a result string from those fields. Then assign the string
to the value of the unbound text box.