[KYaeger]

I have a word letter with merged fields from Access. One field containing
salary sometimes is not what is stored in the Access table. e.g. employees
salary in employees table is $927.20. In the merged document it is listed as
$929.00-even rounded it would not be that. If I remove the field code for
currency formatting it will appear as 927.2. In most cases the merged field
is correct, but occassionally this problem will come up. I have tried
re-entering it in the table, but still wrong in the document. We should be
able to rely on correct information as there are no calculations, only
straight merging form one table. This is Word2003 and Access 2003. Thoughts?

[CyberTaz]

If I interpret your statement correctly:

"If I remove the field code for currency formatting..."

it sounds like the salary is being stored in something other than a Currency
field with currency formatting forced on it through the field's properties.
That is most likely the source of your problem. The field Data Type should
be set to Currency with no change to the defult formatting for the Data
Type.
--
HTH |:)
Bob Jones
[MVP] Office:Mac


"KYaeger" wrote in message
...
I have a word letter with merged fields from Access. One field containing
salary sometimes is not what is stored in the Access table. e.g.
employees
salary in employees table is $927.20. In the merged document it is listed
as
$929.00-even rounded it would not be that. If I remove the field code for
currency formatting it will appear as 927.2. In most cases the merged
field
is correct, but occassionally this problem will come up. I have tried
re-entering it in the table, but still wrong in the document. We should
be
able to rely on correct information as there are no calculations, only
straight merging form one table. This is Word2003 and Access 2003.
Thoughts?

[KYaeger]

I have discovered that the connection was made thru ODBC. Oledb did not seem
to work because there is a database password. It continued to ask for data
source, so ODBC was used. Also when using either ODBC or OLEDB the
formatting does not come over. Many posts occur about this problem with
Word2003 and are all about how to format Word for currency and other data
types. It has to be odne that way. But I have also read in other articles
that if you use DDE for the connection, the formatting is preserved. This
connection also solves the problem of the changing values. I have posted
another post about this serious problem earlier today before I found out that
the default method of connecting to Access was not working, and in fact it
appears that ODBC was used. I can recreate this at any time with a brand new
database and a few fields. But thanks for the thought, it appears to be a
bug with ODBC connections.

"CyberTaz" wrote:

If I interpret your statement correctly:

"If I remove the field code for currency formatting..."

it sounds like the salary is being stored in something other than a Currency
field with currency formatting forced on it through the field's properties.
That is most likely the source of your problem. The field Data Type should
be set to Currency with no change to the defult formatting for the Data
Type.
--
HTH |:)
Bob Jones
[MVP] Office:Mac


"KYaeger" wrote in message
...
I have a word letter with merged fields from Access. One field containing
salary sometimes is not what is stored in the Access table. e.g.
employees
salary in employees table is $927.20. In the merged document it is listed
as
$929.00-even rounded it would not be that. If I remove the field code for
currency formatting it will appear as 927.2. In most cases the merged
field
is correct, but occassionally this problem will come up. I have tried
re-entering it in the table, but still wrong in the document. We should
be
able to rely on correct information as there are no calculations, only
straight merging form one table. This is Word2003 and Access 2003.
Thoughts?

[KYaeger]

Also, WHY does simple formating change the values??? I am talking about
random changing like e.g. 2258.80 becoming $2266.00 in the word document. I
might be abel to understand simple rounding but not this type of change.
This is VERY important because the client uses mail merge for intent to hire
letters and the salaries are going in wrong about 10% of the time.

"CyberTaz" wrote:

If I interpret your statement correctly:

"If I remove the field code for currency formatting..."

it sounds like the salary is being stored in something other than a Currency
field with currency formatting forced on it through the field's properties.
That is most likely the source of your problem. The field Data Type should
be set to Currency with no change to the defult formatting for the Data
Type.
--
HTH |:)
Bob Jones
[MVP] Office:Mac


"KYaeger" wrote in message
...
I have a word letter with merged fields from Access. One field containing
salary sometimes is not what is stored in the Access table. e.g.
employees
salary in employees table is $927.20. In the merged document it is listed
as
$929.00-even rounded it would not be that. If I remove the field code for
currency formatting it will appear as 927.2. In most cases the merged
field
is correct, but occassionally this problem will come up. I have tried
re-entering it in the table, but still wrong in the document. We should
be
able to rely on correct information as there are no calculations, only
straight merging form one table. This is Word2003 and Access 2003.
Thoughts?

[Graham Mayor]

I have tried and failed to reproduce this. Is there any more that you can
tell us about the Access file and how the relevant data is created and
stored?

--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org


KYaeger wrote:
Also, WHY does simple formating change the values??? I am talking
about random changing like e.g. 2258.80 becoming $2266.00 in the word
document. I might be abel to understand simple rounding but not this
type of change. This is VERY important because the client uses mail
merge for intent to hire letters and the salaries are going in wrong
about 10% of the time.

"CyberTaz" wrote:

If I interpret your statement correctly:

"If I remove the field code for currency formatting..."

it sounds like the salary is being stored in something other than a
Currency field with currency formatting forced on it through the
field's properties. That is most likely the source of your problem.
The field Data Type should be set to Currency with no change to the
defult formatting for the Data Type.
--
HTH |:)
Bob Jones
[MVP] Office:Mac


"KYaeger" wrote in message
...
I have a word letter with merged fields from Access. One field
containing salary sometimes is not what is stored in the Access
table. e.g. employees
salary in employees table is $927.20. In the merged document it is
listed as
$929.00-even rounded it would not be that. If I remove the field
code for currency formatting it will appear as 927.2. In most cases
the merged field
is correct, but occassionally this problem will come up. I have
tried re-entering it in the table, but still wrong in the document.
We should be
able to rely on correct information as there are no calculations,
only straight merging form one table. This is Word2003 and Access
2003. Thoughts?

[KYaeger]

Yes, I have done more testing now and narrowing the problem down. Create a
new database. In a table, e.g employees, create a field, I named it salary.
Make it currency data type and format. Put in these numbers:$1983.90,
$927.20, $781.60, $2233.50, and $1355.04, and $1811.75.These are actual
numbers from a governmental db. Now put a database level password on it.
When connecting to this db from Word, it apparently can not use oledb, even
when the option to confirm conversions on open is NOT selected. I guess
because of the password. It appears to default in this instance to use ODBC.
Either by choosing ODBC when the option to confirm conversion is selected, or
by Word defaulting to this connection type, use mail merge in your document
with the salary field. I actually put it in twice, once no formatting and
once with the currency formatting: \ #$#,###.00. And you should see the
error in the formatted only figures with all but the last two numbers which
convert OK. Let me know what you see. If I use DDE, all is OK, but the user
will have to do this each time they use mail merge with a new letter. Also,
if I set the Access db to number and let only Word do the formatting, it
appears OK. However, the user would like it stored in currency for reports,
etc. I think we may end up taking off the password and using network file
security. This will revert back to oledb and is also OK. It seems to be
when Word ues ODBC that we have the problem.

Karen
"Graham Mayor" wrote:

I have tried and failed to reproduce this. Is there any more that you can
tell us about the Access file and how the relevant data is created and
stored?

--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org


KYaeger wrote:
Also, WHY does simple formating change the values??? I am talking
about random changing like e.g. 2258.80 becoming $2266.00 in the word
document. I might be abel to understand simple rounding but not this
type of change. This is VERY important because the client uses mail
merge for intent to hire letters and the salaries are going in wrong
about 10% of the time.

"CyberTaz" wrote:

If I interpret your statement correctly:

"If I remove the field code for currency formatting..."

it sounds like the salary is being stored in something other than a
Currency field with currency formatting forced on it through the
field's properties. That is most likely the source of your problem.
The field Data Type should be set to Currency with no change to the
defult formatting for the Data Type.
--
HTH |:)
Bob Jones
[MVP] Office:Mac


"KYaeger" wrote in message
...
I have a word letter with merged fields from Access. One field
containing salary sometimes is not what is stored in the Access
table. e.g. employees
salary in employees table is $927.20. In the merged document it is
listed as
$929.00-even rounded it would not be that. If I remove the field
code for currency formatting it will appear as 927.2. In most cases
the merged field
is correct, but occassionally this problem will come up. I have
tried re-entering it in the table, but still wrong in the document.
We should be
able to rely on correct information as there are no calculations,
only straight merging form one table. This is Word2003 and Access
2003. Thoughts?

[Graham Mayor]

If I follow your example, I now see what you see - but I am not sure if
that takes us any farther forward. I guess it's a case of which workaround
the user will be happiest with?

--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org


KYaeger wrote:
Yes, I have done more testing now and narrowing the problem down.
Create a new database. In a table, e.g employees, create a field, I
named it salary. Make it currency data type and format. Put in these
numbers:$1983.90, $927.20, $781.60, $2233.50, and $1355.04, and
$1811.75.These are actual numbers from a governmental db. Now put a
database level password on it. When connecting to this db from Word,
it apparently can not use oledb, even when the option to confirm
conversions on open is NOT selected. I guess because of the
password. It appears to default in this instance to use ODBC. Either
by choosing ODBC when the option to confirm conversion is selected,
or by Word defaulting to this connection type, use mail merge in your
document with the salary field. I actually put it in twice, once no
formatting and once with the currency formatting: \ #$#,###.00. And
you should see the error in the formatted only figures with all but
the last two numbers which convert OK. Let me know what you see. If
I use DDE, all is OK, but the user will have to do this each time
they use mail merge with a new letter. Also, if I set the Access db
to number and let only Word do the formatting, it appears OK.
However, the user would like it stored in currency for reports, etc.
I think we may end up taking off the password and using network file
security. This will revert back to oledb and is also OK. It seems
to be when Word ues ODBC that we have the problem.

Karen
"Graham Mayor" wrote:

I have tried and failed to reproduce this. Is there any more that
you can tell us about the Access file and how the relevant data is
created and stored?

--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org


KYaeger wrote:
Also, WHY does simple formating change the values??? I am talking
about random changing like e.g. 2258.80 becoming $2266.00 in the
word document. I might be abel to understand simple rounding but
not this type of change. This is VERY important because the client
uses mail merge for intent to hire letters and the salaries are
going in wrong about 10% of the time.

"CyberTaz" wrote:

If I interpret your statement correctly:

"If I remove the field code for currency formatting..."

it sounds like the salary is being stored in something other than a
Currency field with currency formatting forced on it through the
field's properties. That is most likely the source of your problem.
The field Data Type should be set to Currency with no change to the
defult formatting for the Data Type.
--
HTH |:)
Bob Jones
[MVP] Office:Mac


"KYaeger" wrote in message
...
I have a word letter with merged fields from Access. One field
containing salary sometimes is not what is stored in the Access
table. e.g. employees
salary in employees table is $927.20. In the merged document it
is listed as
$929.00-even rounded it would not be that. If I remove the field
code for currency formatting it will appear as 927.2. In most
cases the merged field
is correct, but occassionally this problem will come up. I have
tried re-entering it in the table, but still wrong in the
document. We should be
able to rely on correct information as there are no calculations,
only straight merging form one table. This is Word2003 and Access
2003. Thoughts?

[KYaeger]

Yes, I will leave it up to the user -- high maintenance type. She and I are
wondering why the data changes but guess we will never know why, there seems
to be no logic to what numbers change. I will be discussing this with the
network group also, if they can set up appropriate permissions, think our
first try will be to remove the password. They are also having an issue with
the database locking with the error 3734 (Database has been placed in a
state....) when using mail merge, have you seen this and do you have any
thoughts on this? I was wondering if the ODBC connection might also do this.
They did not have these troubles in A97 so are blaming the recent conversion.

"Graham Mayor" wrote:

If I follow your example, I now see what you see - but I am not sure if
that takes us any farther forward. I guess it's a case of which workaround
the user will be happiest with?

--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org


KYaeger wrote:
Yes, I have done more testing now and narrowing the problem down.
Create a new database. In a table, e.g employees, create a field, I
named it salary. Make it currency data type and format. Put in these
numbers:$1983.90, $927.20, $781.60, $2233.50, and $1355.04, and
$1811.75.These are actual numbers from a governmental db. Now put a
database level password on it. When connecting to this db from Word,
it apparently can not use oledb, even when the option to confirm
conversions on open is NOT selected. I guess because of the
password. It appears to default in this instance to use ODBC. Either
by choosing ODBC when the option to confirm conversion is selected,
or by Word defaulting to this connection type, use mail merge in your
document with the salary field. I actually put it in twice, once no
formatting and once with the currency formatting: \ #$#,###.00. And
you should see the error in the formatted only figures with all but
the last two numbers which convert OK. Let me know what you see. If
I use DDE, all is OK, but the user will have to do this each time
they use mail merge with a new letter. Also, if I set the Access db
to number and let only Word do the formatting, it appears OK.
However, the user would like it stored in currency for reports, etc.
I think we may end up taking off the password and using network file
security. This will revert back to oledb and is also OK. It seems
to be when Word ues ODBC that we have the problem.

Karen
"Graham Mayor" wrote:

I have tried and failed to reproduce this. Is there any more that
you can tell us about the Access file and how the relevant data is
created and stored?

--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org


KYaeger wrote:
Also, WHY does simple formating change the values??? I am talking
about random changing like e.g. 2258.80 becoming $2266.00 in the
word document. I might be abel to understand simple rounding but
not this type of change. This is VERY important because the client
uses mail merge for intent to hire letters and the salaries are
going in wrong about 10% of the time.

"CyberTaz" wrote:

If I interpret your statement correctly:

"If I remove the field code for currency formatting..."

it sounds like the salary is being stored in something other than a
Currency field with currency formatting forced on it through the
field's properties. That is most likely the source of your problem.
The field Data Type should be set to Currency with no change to the
defult formatting for the Data Type.
--
HTH |:)
Bob Jones
[MVP] Office:Mac


"KYaeger" wrote in message
...
I have a word letter with merged fields from Access. One field
containing salary sometimes is not what is stored in the Access
table. e.g. employees
salary in employees table is $927.20. In the merged document it
is listed as
$929.00-even rounded it would not be that. If I remove the field
code for currency formatting it will appear as 927.2. In most
cases the merged field
is correct, but occassionally this problem will come up. I have
tried re-entering it in the table, but still wrong in the
document. We should be
able to rely on correct information as there are no calculations,
only straight merging form one table. This is Word2003 and Access
2003. Thoughts?

[tlc_tgo]

I'm having a related problem in Office 2003 SP2. In my case, numeric
customer IDs are translated to a zero. I keep a list of customer contacts
in an EXCEL spreadsheet, including a column of customer IDs that are usually
a six digit numeric code. However, those customers that have a related site
I append a character to the end of their Id. For example if the primary
site ID is 123456 then related site ID will be 123456A. When I use mail
merge to create a form letter that includes the customer ID, the six-digit
numeric codes print fine, but the related site IDs appear as a single zero.

I re-examined the formatting in my customer spreadsheet and realized that
the Customer ID column was formatted as "number". Aha! I said to myself and
changed it to "text". I saved the spreadsheet, re-opened my word template,
refreshed the connection to the spreadsheet, and re-ran the merge. 123456A
still was displayed as a zero. I tried inserting a space before each ID. No
effect. Finally, inserted a non-blank character before the ID. That worked,
but now I have a dorky-looking apostrophe in my printed customer IDs.

Any ideas for a better work around?
-- Thanks


"KYaeger" wrote:

I have a word letter with merged fields from Access. One field containing
salary sometimes is not what is stored in the Access table. e.g. employees
salary in employees table is $927.20. In the merged document it is listed as
$929.00-even rounded it would not be that. If I remove the field code for
currency formatting it will appear as 927.2. In most cases the merged field
is correct, but occassionally this problem will come up. I have tried
re-entering it in the table, but still wrong in the document. We should be
able to rely on correct information as there are no calculations, only
straight merging form one table. This is Word2003 and Access 2003. Thoughts?

[Graham Mayor]

I have cross posted to the mailmerge forum in case Peter Jamieson has not
been watching and has some ideas

--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org


KYaeger wrote:
Yes, I will leave it up to the user -- high maintenance type. She
and I are wondering why the data changes but guess we will never know
why, there seems to be no logic to what numbers change. I will be
discussing this with the network group also, if they can set up
appropriate permissions, think our first try will be to remove the
password. They are also having an issue with the database locking
with the error 3734 (Database has been placed in a state....) when
using mail merge, have you seen this and do you have any thoughts on
this? I was wondering if the ODBC connection might also do this.
They did not have these troubles in A97 so are blaming the recent
conversion.

"Graham Mayor" wrote:

If I follow your example, I now see what you see - but I am not
sure if that takes us any farther forward. I guess it's a case of
which workaround the user will be happiest with?

--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org


KYaeger wrote:
Yes, I have done more testing now and narrowing the problem down.
Create a new database. In a table, e.g employees, create a field, I
named it salary. Make it currency data type and format. Put in
these numbers:$1983.90, $927.20, $781.60, $2233.50, and $1355.04,
and $1811.75.These are actual numbers from a governmental db. Now
put a database level password on it. When connecting to this db
from Word, it apparently can not use oledb, even when the option to
confirm conversions on open is NOT selected. I guess because of the
password. It appears to default in this instance to use ODBC. Either
by choosing ODBC when the option to confirm conversion is selected,
or by Word defaulting to this connection type, use mail merge in
your document with the salary field. I actually put it in twice,
once no formatting and once with the currency formatting: \
#$#,###.00. And you should see the error in the formatted only
figures with all but the last two numbers which convert OK. Let me
know what you see. If I use DDE, all is OK, but the user will have
to do this each time they use mail merge with a new letter. Also,
if I set the Access db to number and let only Word do the
formatting, it appears OK. However, the user would like it stored
in currency for reports, etc. I think we may end up taking off the
password and using network file security. This will revert back to
oledb and is also OK. It seems to be when Word ues ODBC that we
have the problem.

Karen
"Graham Mayor" wrote:

I have tried and failed to reproduce this. Is there any more that
you can tell us about the Access file and how the relevant data is
created and stored?

--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org


KYaeger wrote:
Also, WHY does simple formating change the values??? I am talking
about random changing like e.g. 2258.80 becoming $2266.00 in the
word document. I might be abel to understand simple rounding but
not this type of change. This is VERY important because the client
uses mail merge for intent to hire letters and the salaries are
going in wrong about 10% of the time.

"CyberTaz" wrote:

If I interpret your statement correctly:

"If I remove the field code for currency formatting..."

it sounds like the salary is being stored in something other
than a Currency field with currency formatting forced on it
through the field's properties. That is most likely the source
of your problem. The field Data Type should be set to Currency
with no change to the defult formatting for the Data Type.
--
HTH |:)
Bob Jones
[MVP] Office:Mac


"KYaeger" wrote in message
...
I have a word letter with merged fields from Access. One field
containing salary sometimes is not what is stored in the Access
table. e.g. employees
salary in employees table is $927.20. In the merged document it
is listed as
$929.00-even rounded it would not be that. If I remove the
field code for currency formatting it will appear as 927.2. In
most cases the merged field
is correct, but occassionally this problem will come up. I have
tried re-entering it in the table, but still wrong in the
document. We should be
able to rely on correct information as there are no
calculations, only straight merging form one table. This is
Word2003 and Access 2003. Thoughts?

[Graham Mayor]

From the Tools menu in Word, select Options and then go to the General tab
and check the box against the "Confirm conversions at open" item. Then when
you attach the data source to the mail merge main document, you will be
given the option of using the DDE method of connection which should read the
data as you have it formatted in the table.

--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org


tlc_tgo wrote:
I'm having a related problem in Office 2003 SP2. In my case, numeric
customer IDs are translated to a zero. I keep a list of customer
contacts in an EXCEL spreadsheet, including a column of customer IDs
that are usually a six digit numeric code. However, those customers
that have a related site I append a character to the end of their Id.
For example if the primary site ID is 123456 then related site ID
will be 123456A. When I use mail merge to create a form letter that
includes the customer ID, the six-digit numeric codes print fine, but
the related site IDs appear as a single zero.

I re-examined the formatting in my customer spreadsheet and realized
that the Customer ID column was formatted as "number". Aha! I said
to myself and changed it to "text". I saved the spreadsheet,
re-opened my word template, refreshed the connection to the
spreadsheet, and re-ran the merge. 123456A still was displayed as a
zero. I tried inserting a space before each ID. No effect.
Finally, inserted a non-blank character before the ID. That worked,
but now I have a dorky-looking apostrophe in my printed customer IDs.

Any ideas for a better work around?
-- Thanks


"KYaeger" wrote:

I have a word letter with merged fields from Access. One field
containing salary sometimes is not what is stored in the Access
table. e.g. employees salary in employees table is $927.20. In the
merged document it is listed as $929.00-even rounded it would not be
that. If I remove the field code for currency formatting it will
appear as 927.2. In most cases the merged field is correct, but
occassionally this problem will come up. I have tried re-entering
it in the table, but still wrong in the document. We should be able
to rely on correct information as there are no calculations, only
straight merging form one table. This is Word2003 and Access 2003.
Thoughts?

[Peter Jamieson]

Roughly speaking,
a. there have always been problems with the Currency field type in Access.
I think it's because the underlying representation is not float or double
and that the libary routines that Microsoft uses in Word and possibly
elsewhere do not deal with these numbers correctly, but have never tried to
get to the bottom of it. There is or was a KB article about some aspects of
this but I'm not in a position to look for it right now. The only ways I
know to work around these problems are (1) change the data type of the
database field) and possibly (2) use a query, probably with an explicit
conversion function such as cstr() to return the same value in a different
format. If you need to do calculations on these numbers, do it in the query.
If the query is sufficiently short, you can put it in a Word VBA
OpenDataSource call - otherwise it needs to be in an Access query.
b. to make an OLEDB connection to an Access database with a password you
have to create a suitable .odc file and use that as your data source. The
problem with this is that the password has to be embedded in the .odc, or
the user is presented with the scary data link dialog box and has to know
how to enter the password. if they do that and save the .doc, the password
is embedded in a .doc and is probably rather easily discovered.

A sample .odc as created by the Office data link editor to open a table
called mytable in a database called c:\mydbs\mdb.mdb with a password mydbpw
looks like this:

-----------------------------------------
html

head
meta http-equiv=Content-Type content="text/x-ms-odc; charset=utf-8"
meta name=ProgId content=ODC.Table
meta name=SourceType content=OLEDB
meta name=Table content=mytable
xml id=docprops/xmlxml id=msodcodc:OfficeDataConnection
xmlnsdc="urn:schemas-microsoft-comfficedc"
xmlns="http://www.w3.org/TR/REC-html40"
odc:Connection odc:Type="OLEDB"
odc:ConnectionStringProvider=Microsoft.Jet.OLEDB .4.0;Password="";User
ID=Admin;Data Source=C:\mydbs\mydb.mdb;Mode=Share Deny None;Extended
Properties="";Jet OLEDB:System database="";Jet
OLEDB:Registry Path="";Jet OLEDBatabase Password=mydbpw;Jet
OLEDB:Engine Type=5;Jet OLEDBatabase Locking Mode=1;Jet OLEDB:Global
Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New
Database Password="";Jet OLEDB:Create System Database=False;Jet
OLEDB:Encrypt Database=False;Jet OLEDBon't Copy Locale on
Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet
OLEDB:SFP=False/odc:ConnectionString
odc:CommandTypeTable/odc:CommandType
odc:CommandTextmytable/odc:CommandText
/odc:Connection
/odc:OfficeDataConnection
/xml
style
!--
.ODCDataSource
{
behavior: url(dataconn.htc);
}
--
/style

/head

body onload='init()' scroll=no leftmargin=0 topmargin=0 rightmargin=0
style='border: 0px'
table style='border: solid 1px threedface; height: 100%; width: 100%'
cellpadding=0 cellspacing=0 width='100%'
tr
td id=tdName style='font-family:arial; font-size:medium; padding: 3px;
background-color: threedface'
 
/td
td id=tdTableDropdown style='padding: 3px; background-color:
threedface; vertical-align: top; padding-bottom: 3px'

 
/td
/tr
tr
td id=tdDesc colspan='2' style='border-bottom: 1px threedshadow solid;
font-family: Arial; font-size: 1pt; padding: 2px; background-color:
threedface'

 
/td
/tr
tr
td colspan='2' style='height: 100%; padding-bottom: 4px; border-top:
1px threedhighlight solid;'
div id='pt' style='height: 100%' class='ODCDataSource'/div
/td
/tr
/table


script language='javascript'

function init() {
var sName, sDescription;
var i, j;

try {
sName = unescape(location.href)

i = sName.lastIndexOf(".")
if (i=0) { sName = sName.substring(1, i); }

i = sName.lastIndexOf("/")
if (i=0) { sName = sName.substring(i+1, sName.length); }

document.title = sName;
document.getElementById("tdName").innerText = sName;

sDescription = document.getElementById("docprops").innerHTML;

i = sDescription.indexOf("escription")
if (i=0) { j = sDescription.indexOf("escription", i + 11); }

if (i=0 && j = 0) {
j = sDescription.lastIndexOf("/", j);

if (j=0) {
sDescription = sDescription.substring(i+11, j);
if (sDescription != "") {
document.getElementById("tdDesc").style.fontSize=" x-small";
document.getElementById("tdDesc").innerHTML = sDescription;
}
}
}
}
catch(e) {

}
}
/script

/body

/html

-----------------------------------------

You can chop a lot of that out (I would guess you can reduce the connection
string to

odc:ConnectionStringProvider=Microsoft.Jet.OLEDB .4.0;Password="";User
ID=Admin;Data Source=C:\mydbs\mydb.mdb;Mode=Share Deny None;Jet
OLEDBatabase Password=mydbpw;/odc:ConnectionString

)

If you leave the Jet OLEDBatabase Password=mydbpw; bit out but open the
database with the .odc the user should see the data link dialog. all they
actually have to do is go to the All tab and fill in the correct value for
the Jet OLEDbatabase Password, then press OK. However, this does not
appear to work if you specify the .mdb dirctly as the data source.

If you have problems with putting a password in plain text in a .odc (I can
think of circumstances in which you wouldn't, but not many) then
a. you are probably better off using Windows directory security, i.e.
relying on Windows user security
b. you could try the following:
- make the connection via the .odc route as suggested.
- save and close the .doc
- edit the .odc in Notepad and remove te Jet OLEDBatabase Passowrd
entry
- save the .odc
- reopen the .doc. Here, it seems to remember the previous settings. But
I wouldn't want to rely on it. Although the password may be buried in the
binary .doc file format, it only takes somweone to save it as .rtf or .html
to see the password, or someone to dig around a bit using VBA, and so on.

NB, you have to use a .odc or .udl to open OLE DB data source in Word if you
want to modify anything in the standard connection string other than the
stuff that Word puts in itself. You have to use a .odc rather than a .udl
(as far as I know) if you want to specify a particular table to connect to.

Peter Jamieson


"Graham Mayor" wrote in message
...
I have cross posted to the mailmerge forum in case Peter Jamieson has not
been watching and has some ideas

--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org


KYaeger wrote:
Yes, I will leave it up to the user -- high maintenance type. She
and I are wondering why the data changes but guess we will never know
why, there seems to be no logic to what numbers change. I will be
discussing this with the network group also, if they can set up
appropriate permissions, think our first try will be to remove the
password. They are also having an issue with the database locking
with the error 3734 (Database has been placed in a state....) when
using mail merge, have you seen this and do you have any thoughts on
this? I was wondering if the ODBC connection might also do this.
They did not have these troubles in A97 so are blaming the recent
conversion.

"Graham Mayor" wrote:

If I follow your example, I now see what you see - but I am not
sure if that takes us any farther forward. I guess it's a case of
which workaround the user will be happiest with?

--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org


KYaeger wrote:
Yes, I have done more testing now and narrowing the problem down.
Create a new database. In a table, e.g employees, create a field, I
named it salary. Make it currency data type and format. Put in
these numbers:$1983.90, $927.20, $781.60, $2233.50, and $1355.04,
and $1811.75.These are actual numbers from a governmental db. Now
put a database level password on it. When connecting to this db
from Word, it apparently can not use oledb, even when the option to
confirm conversions on open is NOT selected. I guess because of the
password. It appears to default in this instance to use ODBC. Either
by choosing ODBC when the option to confirm conversion is selected,
or by Word defaulting to this connection type, use mail merge in
your document with the salary field. I actually put it in twice,
once no formatting and once with the currency formatting: \
#$#,###.00. And you should see the error in the formatted only
figures with all but the last two numbers which convert OK. Let me
know what you see. If I use DDE, all is OK, but the user will have
to do this each time they use mail merge with a new letter. Also,
if I set the Access db to number and let only Word do the
formatting, it appears OK. However, the user would like it stored
in currency for reports, etc. I think we may end up taking off the
password and using network file security. This will revert back to
oledb and is also OK. It seems to be when Word ues ODBC that we
have the problem.

Karen
"Graham Mayor" wrote:

I have tried and failed to reproduce this. Is there any more that
you can tell us about the Access file and how the relevant data is
created and stored?

--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org


KYaeger wrote:
Also, WHY does simple formating change the values??? I am talking
about random changing like e.g. 2258.80 becoming $2266.00 in the
word document. I might be abel to understand simple rounding but
not this type of change. This is VERY important because the client
uses mail merge for intent to hire letters and the salaries are
going in wrong about 10% of the time.

"CyberTaz" wrote:

If I interpret your statement correctly:

"If I remove the field code for currency formatting..."

it sounds like the salary is being stored in something other
than a Currency field with currency formatting forced on it
through the field's properties. That is most likely the source
of your problem. The field Data Type should be set to Currency
with no change to the defult formatting for the Data Type.
--
HTH |:)
Bob Jones
[MVP] Office:Mac


"KYaeger" wrote in message
...
I have a word letter with merged fields from Access. One field
containing salary sometimes is not what is stored in the Access
table. e.g. employees
salary in employees table is $927.20. In the merged document it
is listed as
$929.00-even rounded it would not be that. If I remove the
field code for currency formatting it will appear as 927.2. In
most cases the merged field
is correct, but occassionally this problem will come up. I have
tried re-entering it in the table, but still wrong in the
document. We should be
able to rely on correct information as there are no
calculations, only straight merging form one table. This is
Word2003 and Access 2003. Thoughts?

[tlc_tgo]

Dear Mr. Mayor,

Thank you for your suggestion, but alas, it is not sufficient. Using the
DDE method did correct the problem with my customer IDs, but it also altered
the names and addresses of my customers. Many of the names in my customer
list -- particularly those from Eastern Europe use inflections and other
diacritical marks on the standard latin alphabet. DDE either changes these
letters to a base latin character or gives up and replaces it with a question
mark. I need to spell my customer's names and addresses correctly and not
use some English language equivalent.

The OLE method causes the previously mentioned zero replacement for the
customer ID, while the ODBC method converts the customer ID to decimals (e.g.
123456 becomes 123456.0)

Got any other ideas?
Thanks, tlc_tgo.


"Graham Mayor" wrote:

From the Tools menu in Word, select Options and then go to the General tab
and check the box against the "Confirm conversions at open" item. Then when
you attach the data source to the mail merge main document, you will be
given the option of using the DDE method of connection which should read the
data as you have it formatted in the table.

--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org


tlc_tgo wrote:
I'm having a related problem in Office 2003 SP2. In my case, numeric
customer IDs are translated to a zero. I keep a list of customer
contacts in an EXCEL spreadsheet, including a column of customer IDs
that are usually a six digit numeric code. However, those customers
that have a related site I append a character to the end of their Id.
For example if the primary site ID is 123456 then related site ID
will be 123456A. When I use mail merge to create a form letter that
includes the customer ID, the six-digit numeric codes print fine, but
the related site IDs appear as a single zero.

I re-examined the formatting in my customer spreadsheet and realized
that the Customer ID column was formatted as "number". Aha! I said
to myself and changed it to "text". I saved the spreadsheet,
re-opened my word template, refreshed the connection to the
spreadsheet, and re-ran the merge. 123456A still was displayed as a
zero. I tried inserting a space before each ID. No effect.
Finally, inserted a non-blank character before the ID. That worked,
but now I have a dorky-looking apostrophe in my printed customer IDs.

Any ideas for a better work around?
-- Thanks


"KYaeger" wrote:

I have a word letter with merged fields from Access. One field
containing salary sometimes is not what is stored in the Access
table. e.g. employees salary in employees table is $927.20. In the
merged document it is listed as $929.00-even rounded it would not be
that. If I remove the field code for currency formatting it will
appear as 927.2. In most cases the merged field is correct, but
occassionally this problem will come up. I have tried re-entering
it in the table, but still wrong in the document. We should be able
to rely on correct information as there are no calculations, only
straight merging form one table. This is Word2003 and Access 2003.
Thoughts?

[Graham Mayor]

In that case you are going to have to change the data file to use separate
fields for the numbers and letters. The numbers can be tamed with a
formatting switch {Mergefield fieldname \# "0"}{Mergefield letterfield}

--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org


tlc_tgo wrote:
Dear Mr. Mayor,

Thank you for your suggestion, but alas, it is not sufficient. Using
the DDE method did correct the problem with my customer IDs, but it
also altered the names and addresses of my customers. Many of the
names in my customer list -- particularly those from Eastern Europe
use inflections and other diacritical marks on the standard latin
alphabet. DDE either changes these letters to a base latin
character or gives up and replaces it with a question mark. I need
to spell my customer's names and addresses correctly and not use some
English language equivalent.

The OLE method causes the previously mentioned zero replacement for
the customer ID, while the ODBC method converts the customer ID to
decimals (e.g. 123456 becomes 123456.0)

Got any other ideas?
Thanks, tlc_tgo.


"Graham Mayor" wrote:

From the Tools menu in Word, select Options and then go to the
General tab and check the box against the "Confirm conversions at
open" item. Then when you attach the data source to the mail merge
main document, you will be given the option of using the DDE method
of connection which should read the data as you have it formatted in
the table.

--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org


tlc_tgo wrote:
I'm having a related problem in Office 2003 SP2. In my case,
numeric customer IDs are translated to a zero. I keep a list of
customer contacts in an EXCEL spreadsheet, including a column of
customer IDs that are usually a six digit numeric code. However,
those customers that have a related site I append a character to
the end of their Id. For example if the primary site ID is 123456
then related site ID will be 123456A. When I use mail merge to
create a form letter that includes the customer ID, the six-digit
numeric codes print fine, but the related site IDs appear as a
single zero.

I re-examined the formatting in my customer spreadsheet and realized
that the Customer ID column was formatted as "number". Aha! I said
to myself and changed it to "text". I saved the spreadsheet,
re-opened my word template, refreshed the connection to the
spreadsheet, and re-ran the merge. 123456A still was displayed as a
zero. I tried inserting a space before each ID. No effect.
Finally, inserted a non-blank character before the ID. That worked,
but now I have a dorky-looking apostrophe in my printed customer
IDs.

Any ideas for a better work around?
-- Thanks


"KYaeger" wrote:

I have a word letter with merged fields from Access. One field
containing salary sometimes is not what is stored in the Access
table. e.g. employees salary in employees table is $927.20. In
the merged document it is listed as $929.00-even rounded it would
not be that. If I remove the field code for currency formatting
it will appear as 927.2. In most cases the merged field is
correct, but occassionally this problem will come up. I have
tried re-entering it in the table, but still wrong in the
document. We should be able to rely on correct information as
there are no calculations, only straight merging form one table.
This is Word2003 and Access 2003. Thoughts?

[Peter Jamieson]

If you do not mind having the whole column formatted as text, you can try
the solution suggested by Cindy M: Don't just Format|Cells as text,
- select the column
- select the Data|Text to Columns... menu option
- click Next through the wizard until you reach Step 3 of 3, then select
Text as the Column Data Format.

FWIW there is a page of information about problems with Excel data sources
at

http://tips.pjmsn.me.uk/t0003.htm

Peter Jamieson

"tlc_tgo" wrote in message
...
Dear Mr. Mayor,

Thank you for your suggestion, but alas, it is not sufficient. Using the
DDE method did correct the problem with my customer IDs, but it also
altered
the names and addresses of my customers. Many of the names in my customer
list -- particularly those from Eastern Europe use inflections and other
diacritical marks on the standard latin alphabet. DDE either changes
these
letters to a base latin character or gives up and replaces it with a
question
mark. I need to spell my customer's names and addresses correctly and not
use some English language equivalent.

The OLE method causes the previously mentioned zero replacement for the
customer ID, while the ODBC method converts the customer ID to decimals
(e.g.
123456 becomes 123456.0)

Got any other ideas?
Thanks, tlc_tgo.


"Graham Mayor" wrote:

From the Tools menu in Word, select Options and then go to the General
tab
and check the box against the "Confirm conversions at open" item. Then
when
you attach the data source to the mail merge main document, you will be
given the option of using the DDE method of connection which should read
the
data as you have it formatted in the table.

--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org


tlc_tgo wrote:
I'm having a related problem in Office 2003 SP2. In my case, numeric
customer IDs are translated to a zero. I keep a list of customer
contacts in an EXCEL spreadsheet, including a column of customer IDs
that are usually a six digit numeric code. However, those customers
that have a related site I append a character to the end of their Id.
For example if the primary site ID is 123456 then related site ID
will be 123456A. When I use mail merge to create a form letter that
includes the customer ID, the six-digit numeric codes print fine, but
the related site IDs appear as a single zero.

I re-examined the formatting in my customer spreadsheet and realized
that the Customer ID column was formatted as "number". Aha! I said
to myself and changed it to "text". I saved the spreadsheet,
re-opened my word template, refreshed the connection to the
spreadsheet, and re-ran the merge. 123456A still was displayed as a
zero. I tried inserting a space before each ID. No effect.
Finally, inserted a non-blank character before the ID. That worked,
but now I have a dorky-looking apostrophe in my printed customer IDs.

Any ideas for a better work around?
-- Thanks


"KYaeger" wrote:

I have a word letter with merged fields from Access. One field
containing salary sometimes is not what is stored in the Access
table. e.g. employees salary in employees table is $927.20. In the
merged document it is listed as $929.00-even rounded it would not be
that. If I remove the field code for currency formatting it will
appear as 927.2. In most cases the merged field is correct, but
occassionally this problem will come up. I have tried re-entering
it in the table, but still wrong in the document. We should be able
to rely on correct information as there are no calculations, only
straight merging form one table. This is Word2003 and Access 2003.
Thoughts?