Numeric extra digits, with a complex twist

[cjg.groups]

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.

 

[Doug Robbins - Word MVP]

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

 

[cjg.groups]

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.

 

[Peter Jamieson]

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

 

[cjg.groups]

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

 

[jan34748]

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.

 

[Graham Mayor]

Did you update the field after adding the switch?
See http://www.gmayor.com/formatting_word_fields.htm

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>