[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

"Lynda" wrote in message
...
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

"Doug Robbins - Word MVP" wrote in message
...
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

"Lynda" wrote in message
...
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
"Lynda" wrote in message
...
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
"Doug Robbins - Word MVP" wrote in message
...
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" wrote in message
...
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