Access 2007-2019
Access 2000-2003
Access Seminars
TechHelp Support
Tips & Tricks
Access Forum
Course Index
Topic Glossary
Insider Circle
Home   Courses   Seminars   Templates   TechHelp   Fast Tips   Forums   Help   Contact   Join   Order   Logon  
Access / Word Q&A: Format Currency on Mail Merge
By Richard Rost   Richard Rost on Twitter Richard Rost on LinkedIn Email Richard Rost   14 years ago

Q: When I attempt to mail merge Access data (formatted as currency) to my Word document (a contract form I've created to use daily) the currency formatting which is correctly displayed in the Access fields does not transfer to Word. The values are correct but no dollar signs or commas show up and, if the number is .50, it shows up as .5 does not format to 2 decimal places. Please help. >Barry

A: Barry, this is a VERY good question. This is something you'd think that Microsoft would fix, but it's been a problem for several versions of Office now. I don't know if they've fixed it in 2007 yet, but it's still a problem in 2003.

There are two ways to fix this problem. You can fix it in Access or Word. I prefer the Access solution. I'll show you both.

In Access, create a QUERY, and use the FORMAT function to explicitly format your value as a Currency. So, for example, if your field name is OrderTotal, you'll create a new calculated field called MyOrderTotal in a query like this:

MyOrderTotal: Format(OrderTotal, "Currency")

I show you how to do this in Access 311:

Now just point your mail merge to this new query, and everything should run just fine. That's the EASY way to do it.

In Word you can fix it also, but this is a little more involved, and I don't recommend it. You can actually format your merge codes using the "numeric picture switch."

With your mail merge in progress, insert the field into your document. Now, right-click on it and select EDIT FIELD. Now click on the button in the bottom-left corner that says FIELD CODES.

In the Field Codes textbox, change your field so it says:

MERGEFIELD OrderTotal /#$0.00

See that little code I added on the end? That's a switch to format the value as a currency. Now be careful, because if you go back in to edit this field, that code disappears half the time.

Again... I don't recommend the Word fix. Make all of your format changes in Access and you'll be much happier.

Access Word Q A Format Currency on Mail Merge Upload Images   Link  
8 years ago
Thanks BenT; that worked a treat!! :))
Add a Reply

Show All Comments

Start a NEW Conversation
Only students may post right now. Click here for more information on how you can set up an account. If you are a student, please LOG ON first.

You may want to read these articles from the 599CD News:

1/17/2022Access Beginner 2 Links
1/17/2022Access Beginner 2
1/17/2022New Record on Top
1/13/2022Missing References
1/13/2022Access Beginner 1 Links
1/11/2022Access Fast Tips
1/11/2022System Defaults
1/10/2022Multi Select List Box
1/10/2022List Box Filter

Access - index
Excel - index
Word - index
Windows - index
PowerPoint - index
Photoshop - index
Visual Basic - index
ASP - index
My Account
My Courses
Lost Password
Student Databases
Change Email
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search The Site
Code Vault
Collapse Menus
Customer Support
Web Site Tour
Consulting Services
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Video Tutorials
Learning Connection
Idiot's Guide to Excel
Volume Discounts
Payment Info
Terms of Sale
Contact Info
Support Policy
Email Richard
Mailing Address
Phone Number
Fax Number
Course Survey
Blog RSS Feed    Twitter

YouTube Channel    LinkedIn
Keywords: access word format mail merge mailmerge tips  PermaLink