Access 2007-2019
Access 2000-2003
Access Seminars
TechHelp Support
Tips & Tricks
Access Forum
Course Index
Topic Glossary
Insider Circle
 
Home   Courses   Seminars   Templates   Help   TechHelp   Forums   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   13 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 ...it 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: http://www.599cd.com?GOAC311

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 
Lyn 
7 years ago
Thanks BenT; that worked a treat!! :))
Add a Reply
Currency field in Access not showing correctly in Upload Images   Link 
Lyn 
7 years ago
Thanks for the tips here but they are not working for me.
The Access database I want to use has other fields in it, so when I created a Query for the "Payment" field and inserted it, the rest of the fields wouldn't work. Read More...
Add a Reply
Access Word Q A Format Currency on Mail Merge Upload Images   Link 
Adam 
9 years ago
Thank you for the tip. If you type in   \# $#,##0.00 you wll find out that the comma is properly placed on the thousanth mark if you are using big numbers.
Add a Reply

Show Older Comments...
View in Table Format

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.
 
Subscribe
 

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

10/21/2021Conditional Formatting
10/20/2021Text Messages
10/19/2021Late Binding
10/18/2021Open Form
10/17/2021Date Only?
10/16/2021Relink Tables
10/16/2021Access Developer 34
10/16/2021Access Developer 34 Lessons
10/16/2021Access Developer 34
10/10/2021Copy Text from Field
 

Learn
 
Access - index
Excel - index
Word - index
Windows - index
PowerPoint - index
Photoshop - index
Visual Basic - index
ASP - index
Seminars
More...
Customers
 
Account Login
Online Theater
Downloads
Lost Password
Memberships
Student Databases
Change Email
Info
 
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search The Site
Code Vault
Collapse Menus
Help
 
Customer Support
Web Site Tour
FAQs
TechHelp
Consulting Services
About
 
Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Order
 
Video Tutorials
Handbooks
Memberships
Learning Connection
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
Contact
 
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