Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Jan 2001
    Location
    Upstate NY, New York, USA
    Posts
    78
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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...

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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.

  3. #3
    Star Lounger
    Join Date
    Nov 2002
    Location
    Port Talbot, Wales
    Posts
    71
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

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

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

    Regards
    Jambo

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •