Fields in a report

[ConorS]

HI

To keep it simple i have a table. It has three fields.

FirstName (Textfield)
Surname (Textfield)
MainBodyText (Memo)

In each field i have the following data:

FirstName:
Mike

Surname:
Jones

MainBodyText :
Dear Mr . please find the ....


-------------------------------------------------------------------------------------------------------

I am looking to generate a report that will print out the Main Body text , but include the surname field within its MainBody text. i.e.

Report:
Dear Mr Jones. Please find the .....


Notice the inclusion of "Jones"

Is it possible for a memo field to include a reference to anotherfield within the same record. Its kinda like nesting a field within another.

Does anyone know how to do this.

 

[scriverb]

Yes, this can be done in a report. But, with a text or memo field the data that you have posted as an example may be different from record to record. Will it always say Dear Mr. and then have the body next? Or will it say Dear Ms. or Dear Mrs. or any of the above without the period. You see to know exactly where to put the Surname there must be a unique character or set of characters that allow for the correct parsing of the data.

I would be better if you didn't include the Dear xxx. in the memo field but rather had another field that the user had entered the Mr., Mrs., Ms, etc. into. With that in mind we can now parse the fields together to look just like the letter configuration that you want by creating a new field in the reports recordsource query that cancatenizes the fields together as you want them.

TextBody: "Dear " & yourtablename.Title & " " & yourtablename.SurName & ":" & vbCrLf & yourtablename.MainTextBody

Copy and paste the above into your query as a new column and when you receive it it will printout like this:

[blue]Dear Mr. Jones:
Please Find the . . . . [/blue]

Post back if this is what you are looking for.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[ConorS]

Hi thanks that seems to work.

But is there anyway of writing the letter into the memo field and for it to grab the required variables?

E.g.


Memo:
---------------------------------------------------
Dear [variable to grab Title][Variable to grab Surname]

Blah Blah Blah you owe us [variable to grab 'cash' from table 'money owed']
---------------------------------------------------

Would it be better

 

[scriverb]

Because it is difficult to structure something in a MemoField to look as you want it in a letter I have found that seperating the paragraphs of the letter and the data to be displayed is a getter way to go. I usually have a opening paragraph, then data field controls like Date of Invoice, Description of service and Amount Due and Due date, then another closing paragraph with other information as necessary. This way you don't need to incorporate the text and fields all into one field where if you change the size of your text control on your report it doesn't look right.

Example of report layout:

Dear <Title control> <Surname Control>:

<Memofield control>

<invoice date ctl> <invoice desc ctl> <invoice amt ctl>

<closing memofield control>

Sincerely,

yourname
yourbusinessname

Hopefully this example will help.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[dhookom]

You can use the Replace() function. This would allow you to embed something like <FirstName> in the memo field and replace it with the actual field name in a report or query.

I have a complete query by form application at

http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane

that does all of this for you. There is a form that opens with the memo field in a text box. Press a hotkey pops up a list of fields that you can insert into the body of the memo field. When you run the report, the field values are merged into the memo field. The report can be sent to Word if you want more formatting control.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[ConorS]

Are there any other ways of doing this?

 

[dhookom]

Is there a reason why you don't just export the table to a Word Merge format and then use Word to create your letter?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[scriverb]

Yes, you can perform a MailMerge with a specially constructed Word document. In database design window, after creating a query that selects your particular record/s click the button titled OfficeLinks. Select the Merge it with MS Word. Follow the wizard through the process of creating a special Word document with database field bookmarks in the document. This new WORD document along with the datafield bookmarks will merge the records data fields with the hard coded word document information. It is a fairly simple process but you have follow the steps a couple of times to fully understand it and get it right. You see the word document is hardlinked to the path of your database front-end and to the particular query that you have selected. Whenever this document/template is opened in WORD it will retrieve the recordset from your database and the query and populate the bookmarks with the data from the record.

Check out ACCESS help concerning MailMerge and you will surely get all the direction you need to process through this setup.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]