Hi all,

I have an Excel spreadsheet where one column, "Outcome" is set as Date
format (dd-mmm). This column contains different dates or text. I only want
the rows with dates in this column to be in the mail merge.

Under Mail Merge Helper I choose Query Options but don't know to get it to
recognize just the ones with dates. Separately, I have tried things like:
Outcome equal to "**-***", **-***, **"-"***, {dd-mmm}, dd-mmm but no mail
merge happens as "no data records matched".

Anyone know what the "compare to" should be for this Query Option?

(Using Word 2000 on XP) Thx for any suggestions.

cheers,
karen

Easier to add another column to the spreadsheet in which populated the cells
with an Excel formula that will provide a yes/no type result that will be
easier to use as a condition in the mailmerge process.

--
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

"klam" wrote in message
...
Hi all,

I have an Excel spreadsheet where one column, "Outcome" is set as Date
format (dd-mmm). This column contains different dates or text. I only
want
the rows with dates in this column to be in the mail merge.

Under Mail Merge Helper I choose Query Options but don't know to get it to
recognize just the ones with dates. Separately, I have tried things like:
Outcome equal to "**-***", **-***, **"-"***, {dd-mmm}, dd-mmm but no mail
merge happens as "no data records matched".

Anyone know what the "compare to" should be for this Query Option?

(Using Word 2000 on XP) Thx for any suggestions.

cheers,
karen

Hi Doug,

Thx a mint for your reply.

I agree that another column would be easier, but the fundamental problem
remains even if I use Conditional Formatting: what is the formula to get it
to recognize the date/populate a new cell with a "Yes" if the "Outcome"
column is equal to a date?

Any suggestions appreciated.
Karen

"Doug Robbins - Word MVP" wrote:

Easier to add another column to the spreadsheet in which populated the cells
with an Excel formula that will provide a yes/no type result that will be
easier to use as a condition in the mailmerge process.

--
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

"klam" wrote in message
...
Hi all,

I have an Excel spreadsheet where one column, "Outcome" is set as Date
format (dd-mmm). This column contains different dates or text. I only
want
the rows with dates in this column to be in the mail merge.

Under Mail Merge Helper I choose Query Options but don't know to get it to
recognize just the ones with dates. Separately, I have tried things like:
Outcome equal to "**-***", **-***, **"-"***, {dd-mmm}, dd-mmm but no mail
merge happens as "no data records matched".

Anyone know what the "compare to" should be for this Query Option?

(Using Word 2000 on XP) Thx for any suggestions.

cheers,
karen

You could perform a calculation on the Outcome field and the ones containing
text will throw an error. You can then trap that error to produce the result
you want e.g.

{ IF{ =MOD({ MERGEFIELD Outcome \@ "yyyy"} ,1)} "!*" "{ Mergefield
Outcome \@ "dd-MMM" }" }

Field error messages start with an exclamation mark so you can compare with
"!*" i.e. an exclamation mark and following text. The type of calculation
isn't really important.

{ IF{ = ({ MERGEFIELD Outcome \@ "yyyy"} * 1)} "!*" "{ Mergefield
Outcome }" }

would work just as well

I have added this as an example to my web page
http://www.gmayor.com/formatting_wor...m#date_or_text
--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org



klam wrote:
Hi Doug,

Thx a mint for your reply.

I agree that another column would be easier, but the fundamental
problem remains even if I use Conditional Formatting: what is the
formula to get it to recognize the date/populate a new cell with a
"Yes" if the "Outcome" column is equal to a date?

Any suggestions appreciated.
Karen

"Doug Robbins - Word MVP" wrote:

Easier to add another column to the spreadsheet in which populated
the cells with an Excel formula that will provide a yes/no type
result that will be easier to use as a condition in the mailmerge
process.

--
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

"klam" wrote in message
...
Hi all,

I have an Excel spreadsheet where one column, "Outcome" is set as
Date format (dd-mmm). This column contains different dates or
text. I only want
the rows with dates in this column to be in the mail merge.

Under Mail Merge Helper I choose Query Options but don't know to
get it to recognize just the ones with dates. Separately, I have
tried things like: Outcome equal to "**-***", **-***, **"-"***,
{dd-mmm}, dd-mmm but no mail merge happens as "no data records
matched".

Anyone know what the "compare to" should be for this Query Option?

(Using Word 2000 on XP) Thx for any suggestions.

cheers,
karen

Hi Graham,

Thx a mint for your solution!

I'll apologize in advance for being such a simpleton - I know you figured
out a sneaky solution and I don't even know how to implement it!

I finally figured out how and where to insert it, and it's a great solution.
The only thing is now it prints that date out on my envelope. I tried
replacing the second "Outccome" with "First Name" but it prints the date and
first name. I only wanted to use the date as a query not something that is
visible. Any suggestions?

cheers,
-karen

"Graham Mayor" wrote:

You could perform a calculation on the Outcome field and the ones containing
text will throw an error. You can then trap that error to produce the result
you want e.g.

{ IF{ =MOD({ MERGEFIELD Outcome \@ "yyyy"} ,1)} "!*" "{ Mergefield
Outcome \@ "dd-MMM" }" }

Field error messages start with an exclamation mark so you can compare with
"!*" i.e. an exclamation mark and following text. The type of calculation
isn't really important.

{ IF{ = ({ MERGEFIELD Outcome \@ "yyyy"} * 1)} "!*" "{ Mergefield
Outcome }" }

would work just as well

I have added this as an example to my web page
http://www.gmayor.com/formatting_wor...m#date_or_text
--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org



klam wrote:
Hi Doug,

Thx a mint for your reply.

I agree that another column would be easier, but the fundamental
problem remains even if I use Conditional Formatting: what is the
formula to get it to recognize the date/populate a new cell with a
"Yes" if the "Outcome" column is equal to a date?

Any suggestions appreciated.
Karen

"Doug Robbins - Word MVP" wrote:

Easier to add another column to the spreadsheet in which populated
the cells with an Excel formula that will provide a yes/no type
result that will be easier to use as a condition in the mailmerge
process.

--
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

"klam" wrote in message
...
Hi all,

I have an Excel spreadsheet where one column, "Outcome" is set as
Date format (dd-mmm). This column contains different dates or
text. I only want
the rows with dates in this column to be in the mail merge.

Under Mail Merge Helper I choose Query Options but don't know to
get it to recognize just the ones with dates. Separately, I have
tried things like: Outcome equal to "**-***", **-***, **"-"***,
{dd-mmm}, dd-mmm but no mail merge happens as "no data records
matched".

Anyone know what the "compare to" should be for this Query Option?

(Using Word 2000 on XP) Thx for any suggestions.

cheers,
karen

If you use the =ISTEXT() function in Excel, it will return FALSE for the
cells that contain dates and True for the ones that do not.

--
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

"klam" wrote in message
...
Hi Doug,

Thx a mint for your reply.

I agree that another column would be easier, but the fundamental problem
remains even if I use Conditional Formatting: what is the formula to get
it
to recognize the date/populate a new cell with a "Yes" if the "Outcome"
column is equal to a date?

Any suggestions appreciated.
Karen

"Doug Robbins - Word MVP" wrote:

Easier to add another column to the spreadsheet in which populated the
cells
with an Excel formula that will provide a yes/no type result that will be
easier to use as a condition in the mailmerge process.

--
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

"klam" wrote in message
...
Hi all,

I have an Excel spreadsheet where one column, "Outcome" is set as Date
format (dd-mmm). This column contains different dates or text. I only
want
the rows with dates in this column to be in the mail merge.

Under Mail Merge Helper I choose Query Options but don't know to get it
to
recognize just the ones with dates. Separately, I have tried things
like:
Outcome equal to "**-***", **-***, **"-"***, {dd-mmm}, dd-mmm but no
mail
merge happens as "no data records matched".

Anyone know what the "compare to" should be for this Query Option?

(Using Word 2000 on XP) Thx for any suggestions.

cheers,
karen

The field construction tests for the error message and what is placed is
determined by what is between the quotes - here your Outcome field which is
a date.
{ IF{ = ({ MERGEFIELD Outcome \@ "yyyy"} * 1)} "!*" "{ Mergefield
Outcome }" }

You can enter anything you want between those quotes - see my web site link
http://www.gmayor.com/formatting_wor...m#date_or_text

{ IF{ = ({ MERGEFIELD Outcome \@ "yyyy"} * 1)} "!*" "Type this" "Omit
this bit to enter nothing" }


--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org



klam wrote:
Hi Graham,

Thx a mint for your solution!

I'll apologize in advance for being such a simpleton - I know you
figured out a sneaky solution and I don't even know how to implement
it!

I finally figured out how and where to insert it, and it's a great
solution. The only thing is now it prints that date out on my
envelope. I tried replacing the second "Outccome" with "First Name"
but it prints the date and first name. I only wanted to use the date
as a query not something that is visible. Any suggestions?

cheers,
-karen

"Graham Mayor" wrote:

You could perform a calculation on the Outcome field and the ones
containing text will throw an error. You can then trap that error to
produce the result you want e.g.

{ IF{ =MOD({ MERGEFIELD Outcome \@ "yyyy"} ,1)} "!*" "{ Mergefield
Outcome \@ "dd-MMM" }" }

Field error messages start with an exclamation mark so you can
compare with "!*" i.e. an exclamation mark and following text. The
type of calculation isn't really important.

{ IF{ = ({ MERGEFIELD Outcome \@ "yyyy"} * 1)} "!*" "{ Mergefield
Outcome }" }

would work just as well

I have added this as an example to my web page
http://www.gmayor.com/formatting_wor...m#date_or_text
--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org



klam wrote:
Hi Doug,

Thx a mint for your reply.

I agree that another column would be easier, but the fundamental
problem remains even if I use Conditional Formatting: what is the
formula to get it to recognize the date/populate a new cell with a
"Yes" if the "Outcome" column is equal to a date?

Any suggestions appreciated.
Karen

"Doug Robbins - Word MVP" wrote:

Easier to add another column to the spreadsheet in which populated
the cells with an Excel formula that will provide a yes/no type
result that will be easier to use as a condition in the mailmerge
process.

--
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

"klam" wrote in message
...
Hi all,

I have an Excel spreadsheet where one column, "Outcome" is set as
Date format (dd-mmm). This column contains different dates or
text. I only want
the rows with dates in this column to be in the mail merge.

Under Mail Merge Helper I choose Query Options but don't know to
get it to recognize just the ones with dates. Separately, I have
tried things like: Outcome equal to "**-***", **-***, **"-"***,
{dd-mmm}, dd-mmm but no mail merge happens as "no data records
matched".

Anyone know what the "compare to" should be for this Query Option?

(Using Word 2000 on XP) Thx for any suggestions.

cheers,
karen

Hi Doug,

Thx for the reply - I tested it, and it's a good trick for me to keep in my
pocket for the future!

Thx a mint!
karen

"Doug Robbins - Word MVP" wrote:

If you use the =ISTEXT() function in Excel, it will return FALSE for the
cells that contain dates and True for the ones that do not.

--
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

"klam" wrote in message
...
Hi Doug,

Thx a mint for your reply.

I agree that another column would be easier, but the fundamental problem
remains even if I use Conditional Formatting: what is the formula to get
it
to recognize the date/populate a new cell with a "Yes" if the "Outcome"
column is equal to a date?

Any suggestions appreciated.
Karen

"Doug Robbins - Word MVP" wrote:

Easier to add another column to the spreadsheet in which populated the
cells
with an Excel formula that will provide a yes/no type result that will be
easier to use as a condition in the mailmerge process.

--
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

"klam" wrote in message
...
Hi all,

I have an Excel spreadsheet where one column, "Outcome" is set as Date
format (dd-mmm). This column contains different dates or text. I only
want
the rows with dates in this column to be in the mail merge.

Under Mail Merge Helper I choose Query Options but don't know to get it
to
recognize just the ones with dates. Separately, I have tried things
like:
Outcome equal to "**-***", **-***, **"-"***, {dd-mmm}, dd-mmm but no
mail
merge happens as "no data records matched".

Anyone know what the "compare to" should be for this Query Option?

(Using Word 2000 on XP) Thx for any suggestions.

cheers,
karen

Hi Graham,

Thx a mint for the reply...makes complete sense now! It worked like a charm.

I have quite a bit more work to do with this file in creating other
documents from it so bwtn your solution and Doug Robbins' suggestions I think
I have enough info/tricks to manage the tasks...yippee!

I really appreciate your time!!

cheers,
karen

"Graham Mayor" wrote:

The field construction tests for the error message and what is placed is
determined by what is between the quotes - here your Outcome field which is
a date.
{ IF{ = ({ MERGEFIELD Outcome \@ "yyyy"} * 1)} "!*" "{ Mergefield
Outcome }" }

You can enter anything you want between those quotes - see my web site link
http://www.gmayor.com/formatting_wor...m#date_or_text

{ IF{ = ({ MERGEFIELD Outcome \@ "yyyy"} * 1)} "!*" "Type this" "Omit
this bit to enter nothing" }


--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org



klam wrote:
Hi Graham,

Thx a mint for your solution!

I'll apologize in advance for being such a simpleton - I know you
figured out a sneaky solution and I don't even know how to implement
it!

I finally figured out how and where to insert it, and it's a great
solution. The only thing is now it prints that date out on my
envelope. I tried replacing the second "Outccome" with "First Name"
but it prints the date and first name. I only wanted to use the date
as a query not something that is visible. Any suggestions?

cheers,
-karen

"Graham Mayor" wrote:

You could perform a calculation on the Outcome field and the ones
containing text will throw an error. You can then trap that error to
produce the result you want e.g.

{ IF{ =MOD({ MERGEFIELD Outcome \@ "yyyy"} ,1)} "!*" "{ Mergefield
Outcome \@ "dd-MMM" }" }

Field error messages start with an exclamation mark so you can
compare with "!*" i.e. an exclamation mark and following text. The
type of calculation isn't really important.

{ IF{ = ({ MERGEFIELD Outcome \@ "yyyy"} * 1)} "!*" "{ Mergefield
Outcome }" }

would work just as well

I have added this as an example to my web page
http://www.gmayor.com/formatting_wor...m#date_or_text
--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org



klam wrote:
Hi Doug,

Thx a mint for your reply.

I agree that another column would be easier, but the fundamental
problem remains even if I use Conditional Formatting: what is the
formula to get it to recognize the date/populate a new cell with a
"Yes" if the "Outcome" column is equal to a date?

Any suggestions appreciated.
Karen

"Doug Robbins - Word MVP" wrote:

Easier to add another column to the spreadsheet in which populated
the cells with an Excel formula that will provide a yes/no type
result that will be easier to use as a condition in the mailmerge
process.

--
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

"klam" wrote in message
...
Hi all,

I have an Excel spreadsheet where one column, "Outcome" is set as
Date format (dd-mmm). This column contains different dates or
text. I only want
the rows with dates in this column to be in the mail merge.

Under Mail Merge Helper I choose Query Options but don't know to
get it to recognize just the ones with dates. Separately, I have
tried things like: Outcome equal to "**-***", **-***, **"-"***,
{dd-mmm}, dd-mmm but no mail merge happens as "no data records
matched".

Anyone know what the "compare to" should be for this Query Option?

(Using Word 2000 on XP) Thx for any suggestions.

cheers,
karen