Possible Word/Access mailmerge question!

[totalnewbie]

I work as a chef in a hotel. Our restaurant menus are 2 page MS Word documents. We have 6 menus for 7 days. For example, Monday-Saturday menu 1-6, then Sunday becomes menu 1 and the following Monday is menu 2 and so on. These menus are changed only every quarter. The thing is, these 6 menus are stored as 6 separate word documents and we have to manually rename them every week and put in new dates. It’s tedious and sometimes open user errors! Is there a way to automate this, maybe some kind of mail merge? The only thing that needs to change in each menu document is the date title at the top. This is currently in the format “Tuesday 27th June 2006”. We could possibly use the Word date format “Tuesday, 27 June 2006”, but prefer the ‘th’ suffix. I was thinking of setting up some kind of table in Access with a form asking the start date and menu number and mail merging it in Word somehow! Any ideas much appreciated.

 

[macropod]

Hi totalnewbie,

Depending on the specifics, you could do this with either a PRINTDATE field or a date calculation field in Word, including the ordinals.

To see how to do either of the above, check out my Date Calc 'tutorial', at:

http://www.wopr.com/cgi-bin/w3t/showthreaded.pl?Number=249902

Cheers

[MS MVP - Word]

 

[totalnewbie]

Thanks for the reply. I was hoping for a detailed how-to on this one. I am a newbie, so I got lost from the start! The link just goes on about date conversions...

 

[SkipVought]

Hi,

You could have code in the DocumentClose event to save it like
[tt]
SaveAs "Menu1_" & Format(Date+6,"yyyy-mm-dd") & ".doc"
[/tt]
save Menu1 as Menu1_2006-07-03.doc

Skip,
[sub]
[red]Be Advised![/red] The only distinction between a bird with one wing and a bird with two, is merely...
a difference of A Pinion! [/sub]

 

[macropod]

Hi totalnewbie,

The 'how to' instructions are in the file I pointed you to.

Cheers

[MS MVP - Word]

 

[jfhewitt]

If what you want to do is simply add the weeks dates to a series of menus for a particular week. For example, every Sunday you want to print the weeks menus from Sunday thru Saturday, automatically. Say you have 7 Word docs, each containing menus for each day of the week. Create an Excel spreadsheet with the heading for each day of the week. In cell A1 enter "TODAY()", which will return todays date. You must print the weeks menus only on Sundays, since the date in cell A1 will update. In cells A2, etc, enter "TODAY()+ 1", "TODAY()+2", ETC. Using Copy and Edit/Paste Special/Paste Link, copy the relevant cell formula to the appropriate Word document. Then, every Sunday your menu documents will be dated for that week.

 

[PeterMoran]

Hi totalnewbie,

Please allow me to add a little to this discussion.

Firstly you need to only read two paragraphs of Macropod's fantastic document to do what you want:
- Viewing and Updating Fields on page 1,
- Insert A Date With Text Using A Form Field on page 2

Now once you understand this, then go to page 2 of Macropod's doc and select the first date and then press Shift F9 to see the actual code in the Field. then with the code selected press Shift F9 again and see how it now displays the date rather than the field code. Nifty eh!

Now with the date selected copy it and paste it into a spare copy of your menu1 document where the date currently is. Now whenever you open this document it will show as today's date in that format without doing anything!!!
We are just about there!

If you really will only accept the exact format you currently have, without the "the" and the "of", then you will have to edit the code in the field. Press Shift F9 to display the code and carefully edit the code to delete the "the" plus one of the spaces around it - leave both quotes and one space. Repeat this with the "of". Then select the whole field and press Shift F9 to display the modified date.

Now we just need some more detailed instructions to explain how to install Skip's code in the DocumentClose event, so that when the document is saved it happens with the date when it is next to be used as part of the filename. I'm sure Skip would help us with that.

Good Luck!

Peter Moran

 

[PeterMoran]

Hi totalnewbie,

In my note I omitted to mention that if you change the format, as discussed in the second last para, you finally need to select the whole field and then press F9 to update the field - it will continue to show the old format until you do this.

Good Luck!

Peter Moran

 

[totalnewbie]

Thanks very much for your excellent help so far everyone, esp. macropod's date codes. Please bear with me! I managed to figure out how to edit the date to get it in the exact format I need (before the later posts). It now looks like this:

{QUOTE{DATE\@ “dddd, d”}{IF{(mod({DATE \@ d},10)<4)*(mod({DATE \@ d},10)<>0*({DATE \@d}<>13)}= 1 {=mod({DATE \@ d},10)-2 \# rd;st;nd}th }{DATE \@ “ MMMM yyyy”}}

This Produces: Tuesday, 4th July 2006

This I have now placed at the top of each menu as a form field. I'm now stuck on how to do the other hard bit!

I would ideally like to input a start date and a menu number (which corresponds to the menu file name 1 to 6). This then needs to generate the 7 new files with the correct dates in each of them. Eg. Start date 07-04-2006, start menu number 4. This should ideally generate something like this:

Menu 07-04-2006.doc (menu 4.doc with 07-04-2006 date title)
Menu 07-05-2006.doc (menu 5.doc with 07-04-2006 date title)
Menu 07-06-2006.doc (menu 6.doc with 07-04-2006 date title)
Menu 07-07-2006.doc (menu 1.doc with 07-04-2006 date title)
Menu 07-08-2006.doc (menu 2.doc with 07-04-2006 date title)
Menu 07-09-2006.doc (menu 3.doc with 07-04-2006 date title)
Menu 07-10-2006.doc (menu 4.doc with 07-04-2006 date title)

 

[totalnewbie]

Sorry, forgot to increment the dates in date title. I can't seem to edit my post...

 

[macropod]

Hi totalnewbie,

For your application, I'd suggest using the field from the section titled 'Calculate a Stepped Date' and changing the 'SET Weekday' variable to suit the particular day's menu.

Alternatively, and somewhat easier to maintain, you could put all the menus into a single document, with section breaks separating each day's menu from the others. Then you could use either:
. a series of the field from the section titled 'Calculate a Stepped Date', with the 'SET Weekday' variable increased by one for each day; or
. a single copy of the field from the section titled 'Calculate a Stepped Date' for the first day, combined part of the field from the section titled 'Calculate a Stepped Date Range' for each of the other days. To use that field, you'd delete all the lines from '{SET Delay 6}' to '32 through 32', inclusive and change the word 'Delay' on the next line to '1'.

The advantage of having all the menus in one document is that you only have one document to update each week and, having defined and bookmarked a menu item once, you can replicate it on other days via a simple cross-reference. The latter also means you can replace all instances of a given item by updating it just once for all days.

Cheers

[MS MVP - Word]