Need Help with Word 2007 MailMerge Using Access as data source.

[Lynda]

Hello, I am having quite a problem with the way a calculated field in an
Access 2007 query is showing up in a Word 2007 Mail Merge document.

In MS Access query, I calculate the decile value of a student's class rank.
For instance, if there is no class rank, the decile is 0. If the class rank
is 8 out of 100, the decile is 1 and so forth. This all works correctly in
Access.

However, when I merge this query into a Word 2007 document, the deciles all
show up as 0 or 10. Somewhere along the way, Word 2007 fails to read the
calculated field correctly.

How can I get the correct results to show up in a mail merge document?

Thanks for your help on this.

Lynda

 

[Doug Robbins - Word MVP]

Use the Format() function in the query in Access to convert the result of
the calculation 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

 

[Lynda]

Can you give me an example of the format function. It would be
format([decile],?)

I can't seem to find what I should be using to convert a number to text.

Thanks.

Lynda

 

[Lynda]

Ok, I found an example of the format() function. I tried it and it worked
great in my Access 2007 query, but failed to display correctly in Word
2007--still displays as a 10.

Any ideas?

Lynda

Can you give me an example of the format function. It would be
format([decile],?)

I can't seem to find what I should be using to convert a number to text.

Thanks.

Lynda

Use the Format() function in the query in Access to convert the result of
the calculation 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

 

[Doug Robbins - Word MVP]

Hi Lynda,

How is the Decile being calculated in Access? Is there a Function in your
database that does the calculation? If so, can you copy and paste the code
that is being used into a message that you post back here so that I can try
and find a method of getting what you want.

Does is always just return 10 or is it returning 10 times what it should be?
If the latter, you could use an { = { Mergefield Decile } / 10 } field
construction.

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

Ok, I found an example of the format() function. I tried it and it worked
great in my Access 2007 query, but failed to display correctly in Word
2007--still displays as a 10.

Any ideas?

Lynda

Can you give me an example of the format function. It would be
format([decile],?)

I can't seem to find what I should be using to convert a number to text.

Thanks.

Lynda

Use the Format() function in the query in Access to convert the result
of the calculation 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

Hello, I am having quite a problem with the way a calculated field in
an Access 2007 query is showing up in a Word 2007 Mail Merge document.

In MS Access query, I calculate the decile value of a student's class
rank. For instance, if there is no class rank, the decile is 0. If the
class rank is 8 out of 100, the decile is 1 and so forth. This all
works correctly in Access.

However, when I merge this query into a Word 2007 document, the deciles
all show up as 0 or 10. Somewhere along the way, Word 2007 fails to
read the calculated field correctly.

How can I get the correct results to show up in a mail merge document?

Thanks for your help on this.

Lynda

 

[Lynda]

Hi:

The calculation is first ClassRank/ClassSize * 100 to get the percentile.
Then the Decile calculation is simply a series of IIF's on the calculated
percentile to determine if the Decile is 0 to 10. This works just great in
Access and returns the correct decile for the particular student.

When the Access data is brought into Word 2007, a 0 shows up correctly, but
all of the other values are 10. I read somewhere that I should use DDE to
get this to work correctly, but I have not tried that yet. I am just
getting used to the intricacies of Word 2007.

Lynda

Hi Lynda,

How is the Decile being calculated in Access? Is there a Function in
your database that does the calculation? If so, can you copy and paste
the code that is being used into a message that you post back here so that
I can try and find a method of getting what you want.

Does is always just return 10 or is it returning 10 times what it should
be? If the latter, you could use an { = { Mergefield Decile } / 10 } field
construction.

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

Ok, I found an example of the format() function. I tried it and it
worked great in my Access 2007 query, but failed to display correctly in
Word 2007--still displays as a 10.

Any ideas?

Lynda

Can you give me an example of the format function. It would be
format([decile],?)

I can't seem to find what I should be using to convert a number to text.

Thanks.

Lynda
Use the Format() function in the query in Access to convert the result
of the calculation 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

Hello, I am having quite a problem with the way a calculated field in
an Access 2007 query is showing up in a Word 2007 Mail Merge document.

In MS Access query, I calculate the decile value of a student's class
rank. For instance, if there is no class rank, the decile is 0. If
the class rank is 8 out of 100, the decile is 1 and so forth. This
all works correctly in Access.

However, when I merge this query into a Word 2007 document, the
deciles all show up as 0 or 10. Somewhere along the way, Word 2007
fails to read the calculated field correctly.

How can I get the correct results to show up in a mail merge document?

Thanks for your help on this.

Lynda

 

[Doug Robbins - Word MVP]

Hi Lynda,

If I use the following function in a query to calculate the Decile

Function Decile(Rank As Long, Class As Long) As Long
Dim Percentile As Long
Percentile = Int(Rank / Class * 100)
Select Case Percentile
Case 1 To 10
Decile = 1
Case 11 To 20
Decile = 2
Case 21 To 30
Decile = 3
Case 31 To 40
Decile = 4
Case 41 To 50
Decile = 5
Case 51 To 60
Decile = 6
Case 61 To 70
Decile = 7
Case 71 To 80
Decile = 8
Case 81 To 90
Decile = 9
Case Else
Decile = 10
End Select
End Function

and then use that query as the data source for a mail merge, the deciles are
displayed correctly.
--
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

Hi:

The calculation is first ClassRank/ClassSize * 100 to get the percentile.
Then the Decile calculation is simply a series of IIF's on the calculated
percentile to determine if the Decile is 0 to 10. This works just great
in Access and returns the correct decile for the particular student.

When the Access data is brought into Word 2007, a 0 shows up correctly,
but all of the other values are 10. I read somewhere that I should use
DDE to get this to work correctly, but I have not tried that yet. I am
just getting used to the intricacies of Word 2007.

Lynda

Hi Lynda,

How is the Decile being calculated in Access? Is there a Function in
your database that does the calculation? If so, can you copy and paste
the code that is being used into a message that you post back here so
that I can try and find a method of getting what you want.

Does is always just return 10 or is it returning 10 times what it should
be? If the latter, you could use an { = { Mergefield Decile } / 10 }
field construction.

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

Ok, I found an example of the format() function. I tried it and it
worked great in my Access 2007 query, but failed to display correctly in
Word 2007--still displays as a 10.

Any ideas?

Lynda
Can you give me an example of the format function. It would be
format([decile],?)

I can't seem to find what I should be using to convert a number to
text.

Thanks.

Lynda
Use the Format() function in the query in Access to convert the result
of the calculation 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

Hello, I am having quite a problem with the way a calculated field in
an Access 2007 query is showing up in a Word 2007 Mail Merge
document.

In MS Access query, I calculate the decile value of a student's class
rank. For instance, if there is no class rank, the decile is 0. If
the class rank is 8 out of 100, the decile is 1 and so forth. This
all works correctly in Access.

However, when I merge this query into a Word 2007 document, the
deciles all show up as 0 or 10. Somewhere along the way, Word 2007
fails to read the calculated field correctly.

How can I get the correct results to show up in a mail merge
document?

Thanks for your help on this.

Lynda

 

[Lynda]

Thanks, I will try that out and let you know how it works for me.

Lynda

Hi Lynda,

If I use the following function in a query to calculate the Decile

Function Decile(Rank As Long, Class As Long) As Long
Dim Percentile As Long
Percentile = Int(Rank / Class * 100)
Select Case Percentile
Case 1 To 10
Decile = 1
Case 11 To 20
Decile = 2
Case 21 To 30
Decile = 3
Case 31 To 40
Decile = 4
Case 41 To 50
Decile = 5
Case 51 To 60
Decile = 6
Case 61 To 70
Decile = 7
Case 71 To 80
Decile = 8
Case 81 To 90
Decile = 9
Case Else
Decile = 10
End Select
End Function

and then use that query as the data source for a mail merge, the deciles
are displayed correctly.
--
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

Hi:

The calculation is first ClassRank/ClassSize * 100 to get the percentile.
Then the Decile calculation is simply a series of IIF's on the calculated
percentile to determine if the Decile is 0 to 10. This works just great
in Access and returns the correct decile for the particular student.

When the Access data is brought into Word 2007, a 0 shows up correctly,
but all of the other values are 10. I read somewhere that I should use
DDE to get this to work correctly, but I have not tried that yet. I am
just getting used to the intricacies of Word 2007.

Lynda

Hi Lynda,

How is the Decile being calculated in Access? Is there a Function in
your database that does the calculation? If so, can you copy and paste
the code that is being used into a message that you post back here so
that I can try and find a method of getting what you want.

Does is always just return 10 or is it returning 10 times what it should
be? If the latter, you could use an { = { Mergefield Decile } / 10 }
field construction.

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

Ok, I found an example of the format() function. I tried it and it
worked great in my Access 2007 query, but failed to display correctly
in Word 2007--still displays as a 10.

Any ideas?

Lynda
Can you give me an example of the format function. It would be
format([decile],?)

I can't seem to find what I should be using to convert a number to
text.

Thanks.

Lynda
Use the Format() function in the query in Access to convert the
result of the calculation 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

Hello, I am having quite a problem with the way a calculated field
in an Access 2007 query is showing up in a Word 2007 Mail Merge
document.

In MS Access query, I calculate the decile value of a student's
class rank. For instance, if there is no class rank, the decile is
0. If the class rank is 8 out of 100, the decile is 1 and so forth.
This all works correctly in Access.

However, when I merge this query into a Word 2007 document, the
deciles all show up as 0 or 10. Somewhere along the way, Word 2007
fails to read the calculated field correctly.

How can I get the correct results to show up in a mail merge
document?

Thanks for your help on this.

Lynda

 

[Lynda]

Hello:

Thanks so much for the solution. It works perfectly!

Lynda