Access / Word Q&A: Format Currency on Mail Merge
By 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.
Show Older Comments...
View in Table Format