Calculating Dates/Merge Fields (VB/Office 97/Word)

Topic

New Reply

Here I go again with another date calculation question:

1. I have a template that pulls merge information from an Access 97 database. One of the fields is called “DUE” and is a date.

2. Before the merge runs, I am running a VB program to elicit some answers from the user as to what text to include in the letter.

3. One of the fields needs to take the mergefield DUE and subtract 6 weeks and insert that calculated date into the letter during the merge.

Question #1:
I am having a problem figuring out how to grab the value of mergefield “DUE”

Question #2
Once I have the value of mergefield DUE, what is the best way to calculate six weeks prior?

Many thanks from this novice…

Reply | Quote

Replies

If you can modify the Access database, the easiest way is to create a query (in Access) that returns the desired results. Include a calculated field

SixWeeksBefore:[Due]-42

in the query. Dates use a day as unit, so subtracting six weeks is the same as subtracting 42. Then use this query as data source for the mail merge.

If it is not possible to create a query in Access, you can use VBA to construct a SQL statement (as string of the form “SELECT … FROM … WHERE …”) that includes a calculated field Due-42 AS SixWeeksBefore and use the SQL string as data source.

Reply | Quote

Or you could use the DateAdd(interval, number, date) function in VB.

i.e. SixWeeksBefore = DateAdd(“ww”,-6,myDate)

Regards
Jambo

Reply | Quote