Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Next Record If by date?
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. -- Thanks, Teresa |
#2
|
|||
|
|||
Hi ?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 :-) |
#3
|
|||
|
|||
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. -- Thanks, Teresa "Cindy M -WordMVP-" wrote: Hi ?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 :-) |
#4
|
|||
|
|||
Hi ?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) 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 :-) |
#5
|
|||
|
|||
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. -- Thanks, Teresa "Cindy M -WordMVP-" wrote: Hi ?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) 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 :-) |
#6
|
|||
|
|||
Hi ?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 :-) |
#8
|
|||
|
|||
Hi ?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 :-) |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to keep date issued (not created) on protected form? | Microsoft Word Help | |||
Wrong mail merge date format | Mailmerge | |||
Insert Date of Creation | New Users | |||
How do I insert a current date in a template and then keep this i. | Microsoft Word Help | |||
using date field in merge | Mailmerge |