X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Adding Query Results To Text In A Report, Access 2010    
post Feb 26 2018, 11:03 AM

Posts: 17
Joined: 4-May 17

Hi all,

Is it possible, just like you would do on a mail merge in Word, to have a report that is predominantly text, but that has inserts which refers to the results of a query.

For example, a query may return the result of a persons name and address, and I would need this to form part of the report not just in an address format, but also int he body of the letter. What would be the best way to do this?


[Title] [Forename] [Surname}

[Add 1]
[Add 2]
[Add 3]
[Add 4]
[Add 5]


Dear [Title] [Surname],

With reference to your [Account Type] membership with us, we would like to invite you and a guest to a banquet celebrating our 10th Anniversary. The invite is strictly for yourself [Forename] as we are keen to only have account holders at the event.

Don't take the above example literally as I wouldn't be using access for something like this!



Go to the top of the page
post Feb 26 2018, 11:07 AM

Access Wiki and Forums Moderator
Posts: 73,941
Joined: 19-June 07
From: SunnySandyEggo

Hi Rob,

For one or two things only, you can just use DLookup().
Go to the top of the page
post Feb 26 2018, 11:10 AM

Posts: 17
Joined: 4-May 17

How would that look on the report (using the example above)? I have used DLookup before but not in this context

P.S. everything I have leant about access is pretty much from these forums!
Go to the top of the page
post Feb 26 2018, 11:19 AM

Access Wiki and Forums Moderator
Posts: 73,941
Joined: 19-June 07
From: SunnySandyEggo

For example:

="Dear " & DLookup("Title", "QueryName") & " " & DLookup("Surname", "QueryName") & ","

Hope it helps...
Go to the top of the page
post Feb 26 2018, 11:42 AM

Posts: 1,184
Joined: 26-January 06
From: .....the wiregrass (either you know or you don't)

Assuming you already have the mailmerge list in a table.....it is very straightforward.

Make a report with all of the static text in a label box. It will take some tweaking because each time you have a field item you will have to end your label box, put in a text box, and then start up again with label box. Some of your label boxes may be quite large if it is a long way between text boxes...Only problem with this is the width of the text box. It is not dynamic, so if the field length is highly variable you will need a wide box but sometimes only put in a short piece of data.

You can get around that. This is a cool trick. If you make a global function (Access calls it a 'public' function or routine) contained in a VBA module that is not a form module. You can make that routine the control source for a text box in the report. Do you see where this is going? When the report reaches that box to update it....it calls out to you (well, your function) and you can return whatever text you like in that box. So, in VBA you can format the dynamic field data into the static text data without any extra space..and it looks like a true/real letter versus a form letter. Just make sure to click yes for 'can grow' in the text box so it you have a long letter it will all fit.

Make the function accept as many fields as you have in your letter. (or you can set it up to allow overloading)..and just have that in the control source passed to your function. Like this.....

control source:

In truth.....I would have separate text boxes for the addresses and name already laid out where they belong in the report and would not call the function for them. But using this method for the body of the report is really slick.
Oh, By sending the reports name in the call you can make the function have multiple texts for multiple form letters.
This post has been edited by zaxbat: Feb 26 2018, 11:53 AM
Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    14th December 2018 - 09:37 AM