Graphical Display of Score

Topic

New Reply

I have a database of chldren and their parents, who have been participants in a research study. At the conclusion, they will undergo an assessment, which results in a numerical score.
The project wants to write to the parents and tell them how the kids went, but they don’t want to actually reveal the numerical score. Instead they want to display it in a manner, like I have shown below.
(This is just a rough prototype and the score would not be visible on the final product.)

Normally I would write to the parents via a Mail Merge. So my question is: Can I put a diagram like that above in a merge document, and have the position of the marker determined by the score.

I could do the letter as an Access report. In that case I would use the following code in the Format Event of the Detail section of the report. (The screen shot above was created that way.)

Code:

Dim lngLeft As Long
Dim lngwidth As Long
lngwidth = Me.BoxRange.Width
Me.LabelMarker.Left = Me.BoxRange.Left + CLng(lngwidth * Me.score / 100 - Me.LabelMarker.Width / 2)

I presume I could use similar code in Word, but I don’t know where to put it. The code would need to execute for each merge record.

Reply | Quote

Replies

There are a number of application-level events relating to MailMerge (attached illustration is from Word 2002). I haven’t done any programming around merge, but these look like a good place to start.

Gary

Reply | Quote

Can I put a diagram like that above in a merge document, and have the position of the marker determined by the score.

Here’s a thought: Is it possible to merge a value into an {ADVANCE} field to create white space to the left (or right) of the image? See Field codes: Advance field – Word – Microsoft Office Online. That seems easier than formatting the position of the image itself, and less hassle than creating a bunch of images and using {INCLUDEPICTURE}.

Reply | Quote

Thanks for those two suggestions. I will look into this and consider my options.

Reply | Quote

Shortly after asking this (10 months ago) the requirement was dropped so I forgot about it…but recently it was resurrected so I have come back to this and I think I have it working.

Code:

Option Explicit
 Dim WithEvents MailMergeApp As Word.Application
 Dim lngRectangleLeft As Long
 Dim lngRectangleWidth As Long
 Dim lngStarWidth As Long
 Dim lngScore As Long
Private Sub Document_Open()
 Set MailMergeApp = Word.Application
 lngStarWidth = Me.Shapes("Star").Width
 lngRectangleLeft = Me.Shapes("Rectangle").Left
 lngRectangleWidth = Me.Shapes("Rectangle").Width
End Sub

Private Sub Document_Close()
 Set MailMergeApp = Nothing
End Sub

Private Sub MailMergeApp_MailMergeBeforeRecordMerge(ByVal Doc As Document, Cancel As Boolean)
 	lngScore = Me.MailMerge.DataSource.DataFields.Item(8).Value
 	Me.Shapes("star").Left = lngRectangleLeft + CLng(lngRectangleWidth * lngScore / 100 - lngStarWidth / 2)
 
End Sub

Initially I used the MailMergeAfterRecordMerge event. This seemed to work, but the position of the Marker actually reflected the score of the previous person. Moving to BeforeRecordMerge fixed that.

Another issue was knowing the name of the Rectangle and Star that I inserted into the document. I found that if I selected each in turn then ran this code from the immediate window, I could rename each of them to Star and Rectangle

Code:

ActiveWindow.Selection.ShapeRange(1).Name = "Star"
ActiveWindow.Selection.ShapeRange(1).Name = "Redtangle"

So thanks again for the suggestion that pointed me in this direction.

Reply | Quote

Hi John,

You could probably achieve what you’re after using a field coded as:
{QUOTE{Set Score {MERGEFIELD Mark}}{IF{Score}>= 1 ” “}{IF{Score}>= 2 ” “}{IF{Score}>= 3 ” “}{IF{Score}>= 4 ” “}{IF{Score}>= 5 ” “}{IF{Score}>= 6 ” “}{IF{Score}>= 7 ” “}{IF{Score}>= 8 ” “}{IF{Score}>= 9 ” “}”X”}
where ‘Mark’ is the data field’s name, the ” ” in each string represents a space or tab (bounded by the double quotes) and the “X” is whatever symbol you want to use to represent the score’s position. As coded, the field provides for 10 positions (0-9), though you could add more. If you don’t need fine gradations and the mark is expressed as a score out of 100, you could also embed the mergefiled into a formula, like:
{={MERGEFIELD Mark}/10} or {={MERGEFIELD Mark}/20}

Alternatively, for something that works with finer gradations (as hinted at by Jefferson), you could use code like:
{ADVANCE r{MERGEFIELD Score}}X
or, say:
{ADVANCE r{QUOTE{={ MERGEFIELD QUANTITY}*4.25}}}X

With either of the above approaches, no macros are required.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Thanks I will explore the Advance option. The score is out of 100 I think, and wants fairly fine gradation.

The link Jefferson provided no longer leads to anything useful, and searching for Advance within Word Help did not return any results, so I used the VBA option.

Reply | Quote