Free Lessons
Fast Tips
Topic Index
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.

Comments for Access / Word Q&A: Format Currency on Mail Merge
Age Subject From
8 yearsAccess Word Q A Format Currency on Mail MergeLyn
8 yearsCurrency field in Access not showing correctly in Lyn
10 yearsAccess Word Q A Format Currency on Mail MergeAdam
12 yearsWord 2k7 Field CodesBenT


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.

Check out these other pages that may be of interest to you:

9/27/2022Report Border
9/26/2022First Day of Month
9/23/2022Before Update
9/22/2022Synchronize Subforms
9/20/2022Link Master Fields
9/18/2022Sequential Annual Coding
9/15/2022Resize Forms

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  Access / Word Q&A: Format Currency on Mail Merge