 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Word / Mailmerge and Fax / August 2006Numeric extra digits, with a complex twist
Hello. When merging from Access 2003 to Word 2003, numbers have extra digits. ie: 1.95 in Access shows as 1.94999999 in Word. However, my situation is more complex than usual. The data table in Access comes from SQL constructed in VB. The problem fields may contain and should show many different precisions (ie: 4000, 3.2, 0.99, 0.0001) or text (ie: "<0.4" or "N/A"). Using { MERGEFIELD Numeric \# 0.00 } always gives me two decimal places and, oddly, converts text "<0.4" to numeric 0.40. Is there a merge field switch to simply merge whatever Access shows me? Sure, use DDE... but how? My merge is done in VB using Word.Document.MailMerge.OpenDataSource and .Execute, all from one Access command button with no extra user intervention. Can I convert .OpenDataSource to use DDE, and do so without any extra dialog boxes? Last resort - Can I convert all numbers to text in SQL before building the data table? Thanks in advance for any help. Use the Format() function in Access to cause the values to be converted to text with the format that you require.  SignatureHope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP > Hello. When merging from Access 2003 to Word 2003, numbers have extra > digits. ie: 1.95 in Access shows as 1.94999999 in Word. However, my [quoted text clipped - 14 lines] > Last resort - Can I convert all numbers to text in SQL before building > the data table? Thanks in advance for any help. Thanks for the quick reply. I'm just not sure when to use Format() in my code. The structure of my merge function, all launched from a command button on an Access form, is this: 1. Build recordset of values to be modified. 2. Build SQL "make table" query string using IF, SELECT, and IIF on recordset fields. Calculations are in the SQL. Also include unmodified fields from three tables. 3. Make new querydef using SQL string and execute, creating a temporary table. 4. Open Word template with .OpenDataSource on temporary table. 5. Execute merge, save document, and close Word. Would I have to add a step between 3 and 4 to Format() all numeric fields to text? Or could the step 2 SQL "make table" statement force a text format? Could CStr be used somewhere? Thanks again. > Use the Format() function in Access to cause the values to be converted to > text with the format that you require. [quoted text clipped - 6 lines] > > Doug Robbins - Word MVP I am not sure that using DDE would solve this particular problem because I'm not convinced that it will retrieve the numbers as you see them in Access, particularly since Word may well strip off leading and trailing zeros whatever you do. However, you should be able to force a DDE connection in OpenDataSource using e.g. ActiveDocument.MailMerge.OpenDataSource _ Name:="<database name", _ Connection:="TABLE t1", _ SQLStatement:="SELECT * FROM [t1]", _ SubType:=wdMergeSubTypeWord2000 to connect to a table. Use Connection:="QUERY q1" to connect to a query called q1. Remove the SubType parameter if you're using Word 2000. This should avoid OLEDB/ODBC dialog boxes but you may still see security-related questions. > Would I have to add a step between 3 and 4 to Format() all numeric > fields to text? Or could the step 2 SQL "make table" statement force a > text format? Could CStr be used somewhere? In your Maketable query you should be able to use format type functions as well as IF, IIF etc. (I think!). You can certainly do it in an ordinary SELECT query. However, again, I am not convinced that there is enough information in your data to generate the correctly formatted numbers, particular if there is a mix of numeric and non-numeric data in the column in question. If you maintain an additional column that tells you how many digits precision you are using in that column in each record, maybe it would be easier. Also, when you use a MAKETABLE query, it is possible that Access will generate a column with a numeric data type - I suepct you might have to avoid that by creating your table with the column types you need and inserting data into it as a separate step. (I don't know Access well enough to tell you that stuff). Peter Jamieson > Thanks for the quick reply. I'm just not sure when to use Format() in > my code. The structure of my merge function, all launched from a [quoted text clipped - 26 lines] >> >> Doug Robbins - Word MVP Peter, at first glance, adding SubType to OpenDataSource may have worked. It required no additional changes to my code or templates. I will test further to ensure it merges all the different potential precisions. Doug, thanks for the Format() tip. I will keep that as a backup. Your time was greatly appreciated, thank you. > I am not sure that using DDE would solve this particular problem because I'm > not convinced that it will retrieve the numbers as you see them in Access, [quoted text clipped - 63 lines] > >> > >> Doug Robbins - Word MVP I have had this same problem, merging from Excel, with the merge in Word returning multiple digits after the decimal. I've tried everything I can think of, including the MERGEFIELD Numeric \# 0.00 solution offered in a reply below, but nothing produces the desired 2 digits after the decimal. I know I must be missing something here. > Hello. When merging from Access 2003 to Word 2003, numbers have extra > digits. ie: 1.95 in Access shows as 1.94999999 in Word. However, my [quoted text clipped - 14 lines] > Last resort - Can I convert all numbers to text in SQL before building > the data table? Thanks in advance for any help. Did you update the field after adding the switch? See http://www.gmayor.com/formatting_word_fields.htm Signature<>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org <>>< ><<> ><<> <>>< ><<> <>>< <>><<> > I have had this same problem, merging from Excel, with the merge in > Word returning multiple digits after the decimal. I've tried [quoted text clipped - 22 lines] >> Last resort - Can I convert all numbers to text in SQL before >> building the data table? Thanks in advance for any help. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.