Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Numeric 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. |
#2
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Numeric extra digits, with a complex twist
Use the Format() function in Access to cause the values to be converted to
text with the format that you require. -- Hope 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 wrote in message oups.com... 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. |
#3
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Numeric extra digits, with a complex twist
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. Doug Robbins - Word MVP wrote: Use the Format() function in Access to cause the values to be converted to text with the format that you require. -- Hope 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 |
#4
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Numeric extra digits, with a complex twist
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 wrote in message oups.com... 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. Doug Robbins - Word MVP wrote: Use the Format() function in Access to cause the values to be converted to text with the format that you require. -- Hope 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 |
#5
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Numeric extra digits, with a complex twist
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. Peter Jamieson wrote: 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 wrote in message oups.com... 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. Doug Robbins - Word MVP wrote: Use the Format() function in Access to cause the values to be converted to text with the format that you require. -- Hope 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 |
#6
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Numeric extra digits, with a complex twist
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. " wrote: 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. |
#7
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Numeric extra digits, with a complex twist
Did you update the field after adding the switch?
See http://www.gmayor.com/formatting_word_fields.htm -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org jan34748 wrote: 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. " wrote: 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. |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Mergefield formatting codes seem to combine digits in numeric data | Mailmerge | |||
numeric pictu formating merge field without rounding decimal digits? | Mailmerge | |||
Extra digits in merge from Excel (Office 2003) | Mailmerge |