operator precedence

S

Steve Smith

In the filter and sort panel what is the order of operation precedence for
AND and OR? Can you truly make complex filters. For example: I'm making an
email merge and want to sent it to everyone with CODE = ABCD or EFGH and
RANGE of 40 to 43 if CODE is ABCD and RANGE of 21 to 24 if CODE is EFGH.
I've tried:
FIELD = ABCD
AND RANGE >=40
AND RANGE <= 43
OR FIELD = EFGH
AND RANGE >=21
AND RANGE <= 24

This seems to work at first glance. The Mail Merge Recipients box shows the
records I expect it to. The problem comes when I click OK. I don't see the
records I'm expecting to see at that point. When I open up the recipients
box again I find out that Word has added AND RANGE IS BLANK between my AND
RANGE >=XX and AND RANGE <= XX lines. It has also changed the AND RANGE <=
YY lines to OR RANGE <= YY.

Is this a bug or is there a way to do what I'm trying to do?
 
M

macropod

Hi Steve,

You could use a SKIPIF field in your mailmerge main document, coded as:
{SKIPIF{={IF{IF{MERGEFIELD ABCD}>= 40 1 0}+{IF{MERGEFIELD ABCD}<= 43 1 0}= 2 1 0}+{IF{IF{MERGEFIELD EFGH}>= 21 1 0}+{IF{MERGEFIELD
EFGH}<= 24 1 0}= 2 1 0}}= 0}
or, using the «ABCD» and «EFGH» mergefields in the document:
{SKIPIF{={IF{IF«ABCD»>= 40 1 0}+{IF«ABCD»<= 43 1 0}= 2 1 0}+{IF{IF«EFGH»>= 21 1 0}+{IF«EFGH»<= 24 1 0}= 2 1 0}}= 0}

Note: The field brace pairs (ie '{ }') for the above example are created via Ctrl-F9 - you can't simply type them or copy & paste
them from this message. Also be careful to preserve the indicated spacing.
 
M

macropod

And for a simpler SKIPIF construction:
{SKIPIF{={IF{IF{MERGEFIELD ABCD}>= 40 1 0}*{IF{IF{MERGEFIELD ABCD}<= 43 1 0}+{IF{MERGEFIELD EFGH}>= 21 1 0}*{IF{MERGEFIELD EFGH}<=
24 1 0}}= 0}
or
{SKIPIF{={IF{IF«ABCD»>= 40 1 0}+{IF«ABCD»<= 43 1 0}= 2 1 0}+{IF{IF«EFGH»>= 21 1 0}+{IF«EFGH»<= 24 1 0}= 2 1 0}}= 0}

--
Cheers
macropod
[MVP - Microsoft Word]


macropod said:
Hi Steve,

You could use a SKIPIF field in your mailmerge main document, coded as:
{SKIPIF{={IF{IF{MERGEFIELD ABCD}>= 40 1 0}+{IF{MERGEFIELD ABCD}<= 43 1 0}= 2 1 0}+{IF{IF{MERGEFIELD EFGH}>= 21 1 0}+{IF{MERGEFIELD
EFGH}<= 24 1 0}= 2 1 0}}= 0}
or, using the «ABCD» and «EFGH» mergefields in the document:
{SKIPIF{={IF{IF«ABCD»>= 40 1 0}+{IF«ABCD»<= 43 1 0}= 2 1 0}+{IF{IF«EFGH»>= 21 1 0}+{IF«EFGH»<= 24 1 0}= 2 1 0}}= 0}

Note: The field brace pairs (ie '{ }') for the above example are created via Ctrl-F9 - you can't simply type them or copy & paste
them from this message. Also be careful to preserve the indicated spacing.

--
Cheers
macropod
[MVP - Microsoft Word]


Steve Smith said:
In the filter and sort panel what is the order of operation precedence for
AND and OR? Can you truly make complex filters. For example: I'm making an
email merge and want to sent it to everyone with CODE = ABCD or EFGH and
RANGE of 40 to 43 if CODE is ABCD and RANGE of 21 to 24 if CODE is EFGH.
I've tried:
FIELD = ABCD
AND RANGE >=40
AND RANGE <= 43
OR FIELD = EFGH
AND RANGE >=21
AND RANGE <= 24

This seems to work at first glance. The Mail Merge Recipients box shows the
records I expect it to. The problem comes when I click OK. I don't see the
records I'm expecting to see at that point. When I open up the recipients
box again I find out that Word has added AND RANGE IS BLANK between my AND
RANGE >=XX and AND RANGE <= XX lines. It has also changed the AND RANGE <=
YY lines to OR RANGE <= YY.

Is this a bug or is there a way to do what I'm trying to do?
Click to expand...
Click to expand...
 
P

Peter Jamieson

I don't know of a way to do it in the User Interface. If macropod's approach
doesn't work for you, you may be able to do what you want in VBA.

Behind the scenes, Word constructs a SQL query and issues that to the data
source. In many cases you can modify the query directly in the VBA editor by
looking at the value of

Activedocument.Mailmerge.DataSource.QueryString

and modifying it or rewriting the SQL.

Word uses a very simple internal dialect of SQL when it queries data sources
that it reads using internal or external converters, e.g. Word documents and
in some cases text files. That dialect is only just rich enough to support
the filtering you can do in the Query Options dialog, so you may not be able
to adjust operator precedence. With Excel/Access and some text files you get
to use Jet SQL which definitely lets you do it (but not if you also need to
connect to Excel using DDE). Other data sources such as SQl Server use their
own dialects.
 
S

Steve Smith

Thanks, I'll try this out.

macropod said:
And for a simpler SKIPIF construction:
{SKIPIF{={IF{IF{MERGEFIELD ABCD}>= 40 1 0}*{IF{IF{MERGEFIELD ABCD}<= 43 1 0}+{IF{MERGEFIELD EFGH}>= 21 1 0}*{IF{MERGEFIELD EFGH}<=
24 1 0}}= 0}
or
{SKIPIF{={IF{IF«ABCD»>= 40 1 0}+{IF«ABCD»<= 43 1 0}= 2 1 0}+{IF{IF«EFGH»>= 21 1 0}+{IF«EFGH»<= 24 1 0}= 2 1 0}}= 0}

--
Cheers
macropod
[MVP - Microsoft Word]


macropod said:
Hi Steve,

You could use a SKIPIF field in your mailmerge main document, coded as:
{SKIPIF{={IF{IF{MERGEFIELD ABCD}>= 40 1 0}+{IF{MERGEFIELD ABCD}<= 43 1 0}= 2 1 0}+{IF{IF{MERGEFIELD EFGH}>= 21 1 0}+{IF{MERGEFIELD
EFGH}<= 24 1 0}= 2 1 0}}= 0}
or, using the «ABCD» and «EFGH» mergefields in the document:
{SKIPIF{={IF{IF«ABCD»>= 40 1 0}+{IF«ABCD»<= 43 1 0}= 2 1 0}+{IF{IF«EFGH»>= 21 1 0}+{IF«EFGH»<= 24 1 0}= 2 1 0}}= 0}

Note: The field brace pairs (ie '{ }') for the above example are created via Ctrl-F9 - you can't simply type them or copy & paste
them from this message. Also be careful to preserve the indicated spacing.

--
Cheers
macropod
[MVP - Microsoft Word]


Steve Smith said:
In the filter and sort panel what is the order of operation precedence for
AND and OR? Can you truly make complex filters. For example: I'm making an
email merge and want to sent it to everyone with CODE = ABCD or EFGH and
RANGE of 40 to 43 if CODE is ABCD and RANGE of 21 to 24 if CODE is EFGH.
I've tried:
FIELD = ABCD
AND RANGE >=40
AND RANGE <= 43
OR FIELD = EFGH
AND RANGE >=21
AND RANGE <= 24

This seems to work at first glance. The Mail Merge Recipients box shows the
records I expect it to. The problem comes when I click OK. I don't see the
records I'm expecting to see at that point. When I open up the recipients
box again I find out that Word has added AND RANGE IS BLANK between my AND
RANGE >=XX and AND RANGE <= XX lines. It has also changed the AND RANGE <=
YY lines to OR RANGE <= YY.

Is this a bug or is there a way to do what I'm trying to do?
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Macro to Select text from a point given to a specific word 2
VBA concatenate ranges into a new document 0
VBA multiple list of values for variable 1
Grouping {MERGEFIELD} into 2 or 3 Columns 0
Converting URLs in comments to Markdown-formatted links 2
Removing empty space character in cell 5
Excel concatenate 1
concatenate 1

Top