IF Statement using multiple Expressions with Bolean

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Viperpurple - 18 Dec 2004 21:49 GMT

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

Reply to this Message

Doug Robbins - Word MVP - 19 Dec 2004 00:30 GMT

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;

Signature

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

> 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
[quoted text clipped - 36 lines]
>
> Adam

Reply to this Message

Peter Jamieson - 19 Dec 2004 10:59 GMT

Assuming your "True" comparisons are working (which they should if you are
using the default connection method in Word XP/2003) then the only time you
need to /miss/ a comma is at the very beginning, so try, e.g.

{ SET comma ""
}{ IF { MERGEFIELD "Bolsover" } = "True" "Bolsover{ SET comma ", " }" "" }
}{ IF { MERGEFIELD "Chesterfield" } = "True" "{ REF comma }Chesterfield{ SET
comma ", " }" "" }
}{ IF { MERGEFIELD "Derbyshire Dales" } = "True" "{ REF comma }Derbyshire
Dales{ SET comma ", " }" "" }
}{ IF { MERGEFIELD "High Peak" } = "True" "{ REF comma }High Peak{ SET comma
", " }" "" }
}

where all the {} are the special field braces you can insert using ctrl-F9

Peter Jamieson

>I am trying to create a mail merge using an access data source. The
>problem
[quoted text clipped - 40 lines]
>
> Adam

Reply to this Message

Viperpurpleuk - 19 Dec 2004 14:33 GMT

Peter, Doug,

Cheers for your help, i have used your solution Peter as it was more suited
and has shown me more about how programming in Word is used.

Thanks

Adam

> 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
[quoted text clipped - 36 lines]
>
> Adam

Reply to this Message