Mail Merge from Access-Format phone numbers

[BillyL]

I have set up a Word mail merge document that reads a query in Access. It works fine except that the phone numbers are not formatted in the word document: (555)555-5555, they are simply displayed as numbers:5555555555. I have set the input mask for phone numbers and the phone numbers are formatted correctly in the Access table and query. I am stumped, I figured this would not be an issue.

Thanks

 

[Dreamboat]

Make a new field in your query that says:

="("&left([phonenumberfield],3)&&quot "&mid([phonenumberfield],4,3)&"-"&right([phonenumberfield],4)

That should give you TEXT that is your phone numbers. Unfortunately, input masks are not data, just VIEWS of your data. You could copy the above to get the spaces right, and just change phonenumberfield to the name of your phone number field.

http://www.TheWordExpert.com

 

[euskadi]

You can almost do it in Word by using field codes [alt][F9] or right click and toggle field codes and change your field to look like this:

{MERGEFIELD WorkPhone \# "(###) ### ####"}

That will give you this result:
(202) 555 1212

Unfortunately, I can't get the dash to come in. And if Dreamboat didn't suggest it, it might not be possible.

 

[Dreamboat]

I HATE MERGEFIELDS.

http://www.TheWordExpert.com

 

[BillyL]

Dreamboat:
Your code worked fine but now I have a twist to the problem. There are international phone numbers included in the data. If the Country field is blank then the country is USA, otherwise it is not. I want to format the phone number as text only if the Country field is blank. I tried the following code:

FaxText: IIf([Country]=Null,"(" & Left([fax],3) & &quot " & Mid([fax],4,3) & "-" & Right([fax],4),[Fax])

The result was that I ended up with the fax number data, not formatted. I also tried IIf([Country]="",.... but got the same results.

Any ideas?

 

[Dreamboat]

FaxText: IIf([Country>0,"(" & Left([fax],3) & &quot " & Mid([fax],4,3) & "-" & Right([fax],4),[Fax])

Try changing to >0 instead of null. Let me know if it doesn't work and I'll seriously look it up.

http://www.TheWordExpert.com

 

[Dreamboat]

Oops. Or make the default value of your field (in the table) be null.

http://www.TheWordExpert.com

 

[BillyL]

The code that worked is:
IIf([Country] is Null,"("&Left([fax],3)&....

The key word is "is Null" not "= Null".

Thanks for all of your help. This site is the greatest!

 

[Dreamboat]

I didn't think =Null was right, but it did allow me to enter it...I just assumed your values weren't null.

Good job!! Part of the reason these sites *can* be wonderful is when the asker sticks with it. So many ask and never return, never provide feedback. They think "I asked, they didn't answer, I'll try somewhere else".

http://www.TheWordExpert.com

 

[BillyL]

Dreamboat:
Obviously you are a good instructor.

 

[Dreamboat]

Why thank you! Actually, I don't instruct at the moment, but there was no *title* for Techie.

http://www.TheWordExpert.com