Number problem in Mail Merge field

[Lynda]

I have an Access database query that computes a student's class rank in
deciles. This works fine in Access, but when I merge it in a Word Document,
Word changes the decile. For example, I have computed a decile of 4 for a
student, but Word displays it as a 10. When I look at the underlying record
source in Word, it shows the 4 as a 10 in the Access field.

Can anyone tell me what is going on here? Why doesn't Word see the real
number?

 

[Doug Robbins - Word MVP]

You could try the different methods of connecting to the data source that
will become available if you select Options from the Tools menu in Word and
then go to the General tab and check the box against the "Confirm file
conversions at open" item.

An alternative would be to use a query in access as the data source and
apply the Format() function to the decile field to convert it to text.

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

 

[Peter Jamieson]

Are you in a position to look at the defintions of the relevant tables in
Access?

 

[Lynda]

kGood Morning:

Yes, I am in a position to look at the definitions in the relevant tables.
However, in this case, the decile generated is calculated in the query so is
not a field in a table. The strange thing is that Word reads the same
calculated field correctly in some cases, but incorrectly in others. It
seems to be random. I have tried everything I can think of, but have not
encountered a solution yet. When I look at the data in Word, the number has
already been changed when Word opens the data source--even though the number
in the query in Access is correct.

 

[Peter Jamieson]

BTW, do try Doug's suggestion if you haven't already.

Things worth looking at:
a. assuming the value in the query originates from a table, what is the
data type of the column in that table? Although it seems rather unlikely to
me, if it happens to be defined as a "Currency" type column, there are
problems with that type.
b. does the value actually come from a lookup table (this may seem unlikely
given that we're just talking about a number, but in this case I wondered
whether a lookupmight be used, e.g. to limit the possible values to a
particular subset)
c. of maybe the value is calculated in a function?
d. also perhaps worth modifying the query code so that the value is
returned as a specific type, e.g. using the cint() function.

 

[Lynda]

I am able to get the correct results when I take Doug's suggestion of
confirming the data source on opening. When I chose DDE, then the problem
went away and the true number was retrained. However, this would mean that
everyone I work with would have to change their Word options. I was hoping
for a solution that would be simpler, but this does work.

The value that has been problematic is an integer that is calculated in an
Access query--it is not formatted as currency. I did try the CINT function,
but got the same results with Word changing the number on merge. I even
converted the number to a text field, but Word still saw it differently.

Lynda

 

[Doug Robbins - Word MVP]

Try the other suggestion of applying the Format() function in the query.

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

 

[Peter Jamieson]

Hmm, you could also try cstr().

Can you provide the query SQL?