Mail merge from Access: applying the “input mask” in Word? 1

[Wizdar]

I’m trying to set up a mail merge using an Access database. The database uses input masks for phone numbers and zip codes.

When merged to a Word document, the formatting doesn’t follow: phone numbers are 1234567890 and zip codes are 987654321. I’d like to retain the formatting as if they were printed to an Access report [(123) 456-7890, 98765-4321].

Two considerations: the end document has to be Word (the merged document will be saved), and much of the source is a table in Access 97 linked to an Access XP database. I can’t change the Access 97 source, as it is regularly updated, and I would have to convert the database each time.

Any solution?

 

[Dreamboat]

Easiest:

Instead of using tables, use queries and write expressions like this:

=Format([phone],"000-000-0000&quot

You can also use Word's merge fields, but I find it harder...however, you can probably search right here and find some samples. Anne Troy

http://www.TheOfficeExperts.com

Word and Excel Macros
Coming soon: wX

 

[Wizdar]

Well, I tried every permutation I could think of and no luck.

When I type your expression into the Format field in the query item properties, it wants to convert it to:

"=For"m"at([p"h\on"e],000-000-0000)" [copied exactly, including beginning and ending quotes]

Naturally, I’m using the correct field name where you’ve used the word “phone.” I tried using @ instead of 0 (as with the Format of the field itself). And tried using the input mask in the query item properties.

I can’t seem to find anything in Word's merge fields to produce the desired results.

 

[Dreamboat]

Take the = sign out?

If that doesn't work, I'll set up a test. Anne Troy

http://www.TheOfficeExperts.com

Word and Excel Macros
Coming soon: wX

 

[Wizdar]

Yep. Tried that.

And tried building a small database in XP, just in case there was a problem with the info in the 97 dbs.

I think Bill just hates me, that's all. I know it's personal.

 

[Dreamboat]

LMAO!!

Can you send me a piece of your DB so I can have a look?
Anne@Theofficeexperts.com Anne Troy

http://www.TheOfficeExperts.com

Word and Excel Macros
Coming soon: wX

 

[Dreamboat]

Thanks very much to my coder who always sets me straight!

Phone: Format([RegionPhone],"(000) 000-0000&quot

Zip: Format([Facility_SiteLocationZip],"00000&quot

Sending file back to ya. Anne Troy

http://www.TheOfficeExperts.com

Word and Excel Macros
Coming soon: wX

 

[asherid]

First off, I would like to apologize for my lack of knowledge. I have run into the same problem and I wanted to know exactly where to put the above code into my query in order to have the input mask carry over to the Word mail merge. Please let me know.

Thanks.

Aidan

 

[Dreamboat]

It's not an input mask, Aidan. It's a format.

Anyway, you make a query with your field in it.
But you uncheck THAT field.
YOu make another column in your query.
In the top line of that column you put the expression.

It'll show up as something like this:

Expr: Format([Facility_SiteLocationZip],"00000&quot

And you change the "Expr" part to be the heading you want it to show up as. Maybe FmtZip instead of Zip, cause you can't give it the same name as your original field name. Then you use the FmtZip as the merge field.

Anne Troy

http://www.TheOfficeExperts.com

Word and Excel Macros
Coming soon: wX

 

[asherid]

Thanks for clarifying that Dreamboat! It worked great. I had no idea that you could do that. That's a huge wait off of my shoulders. Thanks again.

Aidan

 

[Dreamboat]

wait?
weight?

or both?

LOL

Glad to help!

Anne Troy

http://www.TheOfficeExperts.com

Word and Excel Macros
Coming soon: wX