Next Record If by date?

[Teresa]

I have an excel spreadsheet listing people with a start date and end date.
I am trying to create a mail merge with Word 2003 only showing the records
with an end date greater than or equal to today's date.

 

[Cindy M -WordMVP-]

Hi =?Utf-8?B?VGVyZXNh?=,

I have an excel spreadsheet listing people with a start date and end date.
I am trying to create a mail merge with Word 2003 only showing the records
with an end date greater than or equal to today's date.

As in "Today's date", no matter which day I do the merge? Or as in "I'm willing
to always tell Word which date today is"?

Doing things with dates in Word is always tricky, and I don't think you can do
this one dynamically in Word without VBA. Setting a filter in Excel might be
easier, but then the connection method needs to change...

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or reply
in the newsgroup and not by e-mail

 

[Teresa]

I was thinking of "Today's Date" no matter which day I do the merge, however
I am willing to do it differently. I was thinking it might be much easier to
simply add an additional column to my excel spreadsheet saying if someone is
a current member or an old member and then only including the current members
on my merge, but I was havig trouble with that too, when I tried it
yesterday. I also like the filter idea, but I don't usually use the merge
feature in word for more than the basics so I don't know how to set that up.

 

[Cindy M -WordMVP-]

Hi =?Utf-8?B?VGVyZXNh?=,

I was thinking of "Today's Date" no matter which day I do the merge, however
I am willing to do it differently. I was thinking it might be much easier to
simply add an additional column to my excel spreadsheet saying if someone is
a current member or an old member and then only including the current members
on my merge, but I was havig trouble with that too, when I tried it
yesterday. I also like the filter idea, but I don't usually use the merge
feature in word for more than the basics so I don't know how to set that up.
--

The way you propose (an additional column) would certainly be easier to work with
than dates, as far as Word is concerned

Can you describe the kind of trouble you were having? Also, tell me what you're
putting in the column.

while you're looking with that, when you're in Word, display the Recipients dialog
box (the one that lists everyone in the data source). Look for that column you're
adding, and click on the arrow next to the column name. You see that you can select
a value for the column? (That is, if there aren't too many different values; if
there are, then you need to choose the "advanced" entry to specify the filter)

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or reply in
the newsgroup and not by e-mail

 

[Teresa]

The filtering and column actually worked perfect. I added a column to excel
with an IF formula to look at the end date column I already had and return a
value of active or expired so it was very easy to filter on. =IF(H2>=TODAY(
),"Active","Expired") It worked like a charm. The only thing I found I
couldn't really do was add more than one switch to a mergefield in word.
When I'm displaying a persons start - end date I was trying to have the " - "
as a field option but I also wanted to use a switch to display the date as
MM/yy. I found that word would only do one or the other not both. I worked
around it by adding a colmun in excel with the " - " for now, but I hate to
clutter my spreadsheet with something like that if I don't have too.

 

[Cindy M -WordMVP-]

Hi =?Utf-8?B?VGVyZXNh?=,

The filtering and column actually worked perfect. I added a column to excel
with an IF formula to look at the end date column I already had and return a
value of active or expired so it was very easy to filter on. =IF(H2>=TODAY(
),"Active","Expired") It worked like a charm. The only thing I found I
couldn't really do was add more than one switch to a mergefield in word.
When I'm displaying a persons start - end date I was trying to have the " - "
as a field option but I also wanted to use a switch to display the date as
MM/yy. I found that word would only do one or the other not both. I worked
around it by adding a colmun in excel with the " - " for now, but I hate to
clutter my spreadsheet with something like that if I don't have too.

Glad things are beginning to work out I'm not sure I understand what you
mean with the " - "? I assume this has to do with how a date is displayed, but
I'm unclear under what circumstances you'd expect to use it, and what the
alternative should be? Word has IT fields, and I suspect you could use that, but
I need more info to show you exactly how.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or reply
in the newsgroup and not by e-mail

 

[Teresa]

Yes it has to do with how the date is displayed. Here is what I have so far
and below that is a sample of what is being returned. There will always be
a start and end date, but the number of members is not always consistant and
the data for each person is very inconsistant. So the mail merge has more
blank listings then current members so if any members get added they will
automatically be included when I mail merge and all of the headings and
spaces are part of the merge fields (MERGEFIELD WORK_FAX \b "Bus. fax: " )
so if someone doesn't have a Bus. fax then it won't say Bus. fax with a blank
after it.

«TITLE» «FIRST_NAME» «MI» «LAST_NAME» «START_DATE»«Space»«END_DATE»
«POSITION», «COMPANYUNIVERSITY»
«WORK_ADDRESS»
«WORK_ADDRESS_2»
«WORK_CITY», «WORK_STATE» «WORK_ZIP»
Bus. phone: «WORK_PHONE»
Bus. fax: «WORK_FAX»
«WORK_EMAIL»
Assistant: «ASSISTANT_NAME» ~ «ASSISTANT_PHONE» ~ «ASSISTANT_EMAIL»
«HOME_ADDRESS»
«HOME_ADDRESS_2»
«HOME_CITY», «HOME_STATE» «HOME_ZIP»
Home phone: «HOME_PHONE_»
Home fax: «HOME_FAX»
«HOME_EMAIL»
Cell Phone: «CELL_PHONE»

Dr. Sam T. Brown 01/01-05/08
General Partner, Small business
The ABC Company
PO Box 1111
Boise, ID 11111-0000
Bus. phone: 208-111-1111
brown@smallbusiness
Assistant: Jean ~ 208-111-1111

111 2nd st.
Moscow, ID 11111
Home phone: 208-311-1111
(e-mail address removed)
Cell Phone: 208-111-1111

 

[Cindy M -WordMVP-]

Hi =?Utf-8?B?VGVyZXNh?=,

Yes it has to do with how the date is displayed. Here is what I have so far
and below that is a sample of what is being returned. There will always be
a start and end date, but the number of members is not always consistant and
the data for each person is very inconsistant. So the mail merge has more
blank listings then current members so if any members get added they will
automatically be included when I mail merge and all of the headings and
spaces are part of the merge fields (MERGEFIELD WORK_FAX \b "Bus. fax: " )
so if someone doesn't have a Bus. fax then it won't say Bus. fax with a blank
after it.

This is a bit "breathless", but let's see if I've understood correctly? If the
Word_Fax mergefield is blank, you want to see nothing at all? but if it contains
an entry, the entire thing should appear?

In the Mail Merge toolbar there's a button "Insert Word field". In that list you
should find "If...Then...Else". click that, select the field, then as the
comparison "is not blank". Since the dialog box won't let you insert the
mergefield, just type some placeholder in the "True" result, and leave the
"False" result empty.

Back in the document, press Alt+F9 to display the field codes. Double-click the
placeholder text for the "True" result. Now insert the Mergefield. The result
should resemble:
{ IF "{Mergefield Work_Fax" } <> "" "{Mergefield Work_Fax}" }

(If the field codes disappear when you insert the merge field, Alt+F9 will turn
them on again. It will also turn them off when you're finished editing the field
codes.)

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or reply
in the newsgroup and not by e-mail