Query Option to recognize date

[klam]

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

 

[Doug Robbins - Word MVP]

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]

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

 

[Graham Mayor]

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_word_fields.htm#date_or_text
--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

 

[klam]

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

 

[Doug Robbins - Word MVP]

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

 

[Graham Mayor]

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_word_fields.htm#date_or_text

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


--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

 

[klam]

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

 

[klam]

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