WORD MERGE from ACCESS

[Pat]

I would love it if this merge works.
I have an employee database in Access with dates and percentages etc.
I have a document created in word.
When I merge to the word document the following happens (these are only some
of the challenges but if I can fix them I will understand how to fix any
others):

In database Vacation is set at 6% in the merge document it shows: 5.99999992-E

In database Date of Birth is: December 10, 2007 in the merge it shows
either 12/10/2007 or 12:10:2007:0000

In the database SIN is: 999 999 999 in the merge it shows as: 999999999

 

[Graham Mayor]

From the Tools menu in Word, select Options and then go to the General tab
and check the box against the "Confirm conversions at open" item. (In Word
2007 it is Office Button > Word Options > Advanced > General > Confirm file
format conversion on open). Then when you attach the data source to the mail
merge main document, you will be given the option of using the DDE method of
connection which should read the data as you have it formatted in the table.

OR

You will need some switches on your merge fields. Toggle the display ALT+F9
from <<fieldname>> to {Mergefield Fieldname}
{Mergefield Vacation \# "0%" }
{Mergefield DOB \@ "MMMM d, yyyy"}
{Mergefield SIN \# "000 000 000"}
http://www.gmayor.com/formatting_word_fields.htm

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

 

[Pat]

Well the first portion of the instructions worked!!
I do not get a message when I attach the data source asking if I want to use
the DDE method of connection (or any message at all for that matter).

I then went to your 2nd suggestion which did not improve the appearance of
the data.

 

[Peter Jamieson]

I do not get a message when I attach the data source asking

if I want to use the DDE method of connection (or any message at all

for that matter).

Typically, after you select your data source in the Select Data Source
dialog box, you then click the Open button.

If you checked the option that Graham mentioned, after you click Open,
you should see an additional dialog box titled "Confirm Source" This
lists the various possible ways that Word thinks you can use to open tat
data source. If the data source is an Access .mdb file, you will
normally see a DDE option (something like "MS Access Databases via DDE
(*.mdb,*.mde). However, if the database is an Access 2007 .accdb file,
you will not see that option unless you /also/ check "Show All." If that
isn't what you see, it's helpful if you can spell out what you do see.

Also, you may find that DDE does not work very well in Office 2007, so
the other approach Graham mentioned is usually to be preferred. But
again, it would probably help if you can spell out what went wrong, or
did not happen.


Peter Jamieson

http://tips.pjmsn.me.uk

 

[Pat]

Thank you for your help.
You are correct the DDE method did not work.
I tried Graham's other suggestion but I do not think I am applying it
correctly.
You asked for more specifics in order to instruct me properly so here goes:

My database table is already formatted as follows:
Field: SIN set as Text with Input Mask 999\ 999\ 999
Field: DateofBirth: set as Date/Time, long date, format: mmmm dd "," yyyy
Field: Vac% set as Number, format: percentage

When I create the merge into my word document the corresponding results are:

Field: SIN 493666789 (no spaces) should be: 493 666 789
Field: DateofBirth: 1970/12/27 should be: December 27, 1970
Field: Vac% 8.00000000002E-2 should be: 8%

I am merging approx. 200 records. These are just a few of the
inconsistencies, once I understand how to fix them I should be able to
correct the rest on my own.

 

[Peter Jamieson]

Let's go step by step.

Open your mailmerge main document.

Use Alt-F9 to show the field codes, so they look something like

{ MERGEFIELD DateOfBirth <stuff> }

Do you see the fields looking like that? If not, then maybe Alt-F9 is
not working as expected.

If you do, what is actually in the field? i.e. what do you have where I
have <stuff> ?

Whatever it is, try changing <stuff> to be what Graham suggested, which is

\@ "MMMM d, yyyy"

then preview you merge data or remerge to a new document. That should at
least give you a date in a format like

January 10, 2009

If it actually gives you

October 1, 2009

for a date that should be January 10, then you will need to create a
query to fix the date format. Precisely how to do that depends on
whether you are in a position to modify the access database and so on.
But let's see if that is necessary first.

That may give you some clues as to what to do about the SIN and Vac%
fields. However, in the case of the Vac% field, you will definitely need
to multiply your number by 100 to get the % in Word. You can do that
using a nested field, e.g.

{ ={ MERGEFIELD "Vac%" }*100 \#0% }

You can create a field like this by using ctrl-F9 to insert each pair of
the special field code braces {}, and typing everything else in the
normal way. You may need to use Alt-F9 to show the field codes again if
they disappear.

Peter Jamieson

http://tips.pjmsn.me.uk

 

[macropod]

Hi Pat,

Try adding switches to your mergefields as follows:
{MERGEFIELD SIN \# "000' '000' '000"}
{MERGEFIELD DateofBirth \@ "MMMM DD, YYYY"}
{MERGEFIELD Vac% \# 0%}