One of the beautiful things about Access
is other Microsoft products
like Excel, Word, and PowerPoint,
You can make a document
with images and formatting
just the way you want
with Microsoft Word.
And then merge data from Access.
hi, this is crystal
This is a Word document
and this is an Access database
It could also be an Excel workbook,
or other data source.
When you open the Word document,
since it's linked to an external data source,
you see that an SQL statement wants to execute.
All fields will be selected
from a table named MAIL_MERGE.
It'll be sorted by the first name and then the last name,
in ascending order.
NameFirstLast is a field name.
What you don't see here
is connection information for the data source.
In this case, that would be the path and filename
of the Access database.
If you move or rename the database,
or open the Word document
on another machine,
you'll be prompted to change the Connection
for the Data Source.
If you leave this dialog box
on the default value of No,
Word won't get the data using the SQL statement,
and it will open without a data source.
To run the SQL and get data, choose Yes.
When the document opens,
you see images and text in a way
that Access has a hard time doing.
Since this is an invitation for an
Independence Day Party,
there is an American flag in the background.
This was actually drawn using VBA
and rendered on an Access report.
The curvy text is WordArt.
Below that is a 2-column table
and then comes the text of the letter.
When it comes to making documents pretty,
Word is where you want to be.
This data is being merged into the Word document
from fields in the Access database.
The data wouldn't necessarily
have to be in Access.
Access can link to all kinds of other formats
like SQL Server, Oracle, text files,
Excel spreadsheets and ranges,
and a whole lot more.
Access is *access*.
This is the name and address
that will also go on the envelope.
The nick name is used in the salutation,
or it would be the first name.
When you click somewhere in a field,
there is gray shading
and that's because the options are set to show
Field Shading When selected.
At the bottom, there are fields that only show
if certain conditions are true.
This contact is a friend,
so they're invited to the after-party.
They are also a close friend, so they're invited
to bring a sleeping bag for camping.
On the Mailings ribbon tab,
in the Preview Results group,
you can go to another record
and see what the results will look like.
Here are the buttons to go to
first, previous, next, or last record.
You can also type a specific record number
and press ENTER
This is the 10th record for the way they're sorted.
So the record number is not like a real record number
that stays with the record like an ID --
it's just the order that they're in.
You can see what the information from different records
will look like in your document.
Now I'll go to the first record
A & M Surplus is a supplier
Everyone who works there is invited
to the fun and music during the day,
but not the after party,
so the invitation doesn't include conditional text.
The third record is for Aldo,
who is also invited to the after-party,
but not the campout.
Lets go back stage to the Word Options
and see how we can control
how fields will look.
Word Options, Advanced.
Under Show document content,
there is a checkbox to show field codes
and an option for Field shading.
I'm checking the box to
show field codes instead of their values
And I'm going to change the Field Shading
from When Selected to Always
Now you can see all the logic
that we're going to build together
from scratch in just a minute.
Displaying the field codes takes more space,
and some of the text appears to go to a second page.
We didn't have a second page before.
When the results show,
it will be all on 1 page again.
Now I'm just going to go back and set the Options
to what I usually keep them at.
Field Shading When selected
and uncheck the box to show field codes
so you can see results again.
Now I'm going to close the document.
This is the way it was
before the merge codes went in.
At the top is a background image of the American flag
that's set to display behind text.
The wavy text was done with WordArt.
and below that is a 2 column table.
You can see gridlines if you select View Gridlines
on the Table tools Layout ribbon.
There is some temporary text to indicate where
field codes should go.
The beauty of using Word to write this
is that it's easy to write things in Word.
Word is 'word', so its easy to write words.
Access is good at managing data,
and it would be hard to get a report
to look exactly like this. You could do it,
but its much easier to do in Microsoft Word.
In the Access database, there is a temporary table
called MAIL_MERGE.
and I'll explain why its a temporary table later on.
It has the information we need for the mail merge document in Word.
These are the fields that we'll be using from Access:
WhoTo is the name to use in the salutation
NameFirstLast is the name for the address block.
MailAddress is the mailing address with line breaks.
TextNbr is an integer of 0, 1, or 2 , in this case,
to indicate what conditional text will be printed.
Now let's add the merge fields to the Word document.
First we need to specify that data will come from Access.
Let's start a mail merge.
Click Start Mail Merge,
and select Step-by-Step Mail Merge Wizard...
A Mail Merge pane opens on the right with steps.
For Select document type, choose Letters,
which is the default, so you don't have to change anything.
This will send letters to a group of people.
Click Next.
For Select starting document,
choose Use the current document,
also the default, and click Next.
For Select recipients,
this is where Access is going to come in,
choose Use an existing list.
which is also the default, and then click Browse¦
A dialog box pops up to collect the name of the data source.
Navigate to the Access database to use
as a source for your merge, and Open.
You'll then be presented with a list of
tables and queries to pick from ...
and hence the temporary table.
You might have a problem with this step!
Perhaps you created a query
but it isn't on the list to pick from!
There's a way to specify the SQL statement,
but that's beyond what we're covering today,
so we need something that will be on the list.
So what do you do?
Close your Word document, as it is, with a new name.
Just keep it the way it was before you added any merge fields
in case you want to go back to it that way.
The quick thing to do is open the Access database
and create a temporary table to use,
and that is what was done in this case.
The table is called MAIL_MERGE
Then when you open Word again,
start the mail merge wizard,
click Next through the steps you've already done
until you get to Browse for the data source.
Then pick your temporary table.
MAIL_MERGE and click Next.
In this dialog box, you can also Sort
This list will be sorted by First name and then by Last name
These fields are combined into a field called NameFirstLast.
Note that you can also do other things here,
like Filter and find duplicates.
Now we're going to insert our first merge field.
Let's go to where we want something to be replaced.
We need a name here.
I'll just highlight that (temporary text) since
I want it to go away when I insert the merge field.
The ribbon we're going to be using
for all these steps is Mailings.
Choose Insert Merge Field from the ribbon,
Choose the field you want, NameFirstLast.
The text we had before is gone,
because it was selected when we did this.
And instead, we see
the field name inside double angle brackets.
If field codes were showing,
we'd see curly brackets,
which are also called braces,
around a keyword called MERGEFIELD,
and then the name of the merge field.
There is a space inbetween everything
{ MERGEFIELD NameFirstLast }
Now let's replace the mailing address.
Select the temporary text
Again, choose Insert Merge Field,
and this time we're picking MailAddress.
This text has multiple lines.
Its in a table, so there's room for it to grow.
The next place we have text to merge
is in the salutation.
Here were' going to insert WhoTo
Now comes the tricky part, the conditional text at the bottom.
We'll write an IF statement
Go to the end of the document
and press Ctrl-F9 to add a field.
Word will add the empty set of braces,
or curly brackets,
to mark where the field starts and ends,
and there'll be 2 spaces beteen them.
Click inside the braces, after the first space
to position the cursor.
Then type if, and then a space.
Then from the ribbon, Insert Merge Field
and choose TextNbr.
This is a number field in the Access database
that is a 0 for a normal document,
1 for the first conditional text,
and 2 for the first and the second.
If its anything but a normal contact,
then they're going to be invited to the after-party.
But now we can't see anything we just put in there!
We need to see the field codes.
So we're going back to Options > Advanced.
In the Show document content section,
we're checking the box to
Show field codes instead of their values
We can also change the Field shading.
I almost always set it to When selected,
sometimes Always,
and very ocassionally Never.
But I almost never use Never.
Now we see what we're doing again.
After IF and then the mergefield name,
type space > space 0
That's the condition to test.
We need a True part and a False part.
We know they will be text,
so for right now,
we're just going to enter "" space ""
and then we'll come back later
and fill in what we want between the quotes
if we want anything.
Inside the first set of quote marks, for true,
paste the text that we've already written in Word.
So I'll just go up, select it, cut it,
go between the quotes and paste it.
What we want between the quotes
for the false part is absolutely nothing,
so we'll leave it as it is.
We'll also write a condition for the last sentence.
That one will show if the text number is > 1.
Print the sentence if its true,
but nothing if its false.
Remember, everything must be delimited
with the space character,
including inside the field braces.
And no commas,
at least not for separating.
You can have them inside the text that you want to display.
Save your letter with a new name.
Now let's view the results.
Turn the display of field codes off
in the Options so we can see the results.
On the ribbon, toggle Preview Results on.
Click the triangles around the record number on the ribbon
to move between records and spot-check the results.
To do the actual merge, click Finish & Merge on the ribbon.
If you're still in the step-by-step wizard,
you can Next, Next till you get to this part also.
We're going to choose Edit Individual documents
and then All records when prompted.
A new Word document is created
with the results of the merge.
There is one page for every record
since our document was one page long.
Obviously this letter is fictional,
in a time when there's no pandemic,
and people can gather together without fear of each other.
This has been a hard year.
If you've suffered or lost loved ones,
my heart is with you.
Even though this lesson isn't about Excel,
I'd like to dedicate it to Smitty,
a great trainer and friend.
If you're building an application,
and want me to connect to you and help you,
I'd love to do that.
Email me.
Thanks for joining me.
Through sharing, we all get better.