Assuming that the fields are in a table named "Places", if you create an
Access query that contains the following SQL, the desired data will be
displayed in a field named "Result" in the query. Use that query as you data
source and use the Result field to display the data in your mailmerge:
SELECT Places.Bolsover, Places.Chesterfield, Places.[Derbyshire Dales],
Places.[High Peak], -1*([Bolsover]+5*[Chesterfield]+10*[Derbyshire
Dales]+20*[High Peak]) AS Score,
IIf([Score]=1,"Bolsover",IIf([Score]=6,"Bolsover,
Chesterfield",IIf([Score]=11,"Bolsover, DerbyShire
Dales",IIf([Score]=15,"Chesterfield, Derbyshire
Dales",IIf([Score]=16,"Bolsover, Chesterfield, Derbyshire
Dales",IIf([Score]=20,"High Peak",IIf([Score]=21,"Bolsover, High
Peak",IIf([Score]=25,"Chesterfield, High Peak",IIf([Score]=26,"Bolsover,
Chestefield, High Peak",IIf([Score]=30," Derbyshire Dales, High
Peak",IIf([Score]=31,"Bolsover, Derbyshire Dales, High
Peak",IIf([Score]=35,"Chesterfield, Derbyshire Dales, High Peak","Bolsover,
Chesterfield, Derbyshire Dales, High Peak")))))))))))) AS Result
FROM Places;
--
Please post any further questions or followup to the newsgroups for the
benefit of others who may be interested. Unsolicited questions forwarded
directly to me will only be answered on a paid consulting basis.
Hope this helps
Doug Robbins - Word MVP
"Viperpurple" wrote in message
...
I am trying to create a mail merge using an access data source. The
problem
i have is printing a comma seperated list using information stored as
seperate fields with a yes/no answer in the database.
e.g.
Fields:
Bolsover
Chesterfield
Derbyshire Dales
High Peak
List Format:
Bolsover, Chesterfield
OR
Chesterfield, Derbyshire Dales, High Peak
So the main point is that i need to list 'Chesterfield' if the
Chesterfield
field is 'True', i have done this but am having real problems adding the
', '
if the value has something preceding it and then missing it out if there
isn't.
Currently using:
{IF {MERGEFIELD "Bolsover"} = "True" "Bolsover" ""}
Tried Using:
{IF {MERGEFIELD "Bolsover"} = "True" "{IF {MERGEFIELD "Chesterfield"} =
"True" ", Chesterfield" "Chesterfield"}" ""}
Sorry for the long post but it was difficult to explain, thanks for
reading
and just let me know if you need more info. I really hope you can help, i
think its quite a challenge.
Regards
Adam
|