Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Moundridge, Kansas, USA
    Posts
    342
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Mail Merge with Current Date (Word 2003/SP 1)

    <P ID="edit" class=small>(Edited by jscher2000 on 24-Feb-06 12:02. [pre] and [/pre] tags and some line breaks (
    ) added to preserve indenting for easier reading; some data censored for security reasons.
    )</P>I have a mail merge document that merges address out of Access into a Word envelope. I have a macro in Word that opens the envelope and starts the merge process. I would like to have some code that automatically enters the current date in the options and I don't have to enter the date every time. Following is the code. BTW, I'm not proficient in code, most of this is from building the macro in Word.
    <pre>Sub BRF()
    '
    ' BRF Macro
    ' Macro recorded 9/28/05 by Weldon Schultz
    '
    ChangeFileOpenDirectory _
    "Cocuments and SettingsWeldon SchultzMy DocumentsWord DocumentsPrisoners"
    Documents.Open FileName:="Bible Request Form 82505.doc", ConfirmConversions:=True, _
    ReadOnly:=False, AddToRecentFiles:=True, _
    PasswordDocument:="", PasswordTemplate:="", Revert:=False, _
    WritePasswordDocument:="", WritePasswordTemplate:="", Format:= _
    wdOpenFormatAuto, XMLTransform:=""
    ActiveDocument.MailMerge.MainDocumentType = wdFormLetters
    ActiveDocument.MailMerge.OpenDataSource Name:= _
    "P:gtGT access dataGospelTractSociety.mdb", ConfirmConversions:=False, _
    ReadOnly:=False, LinkToSource:=True, AddToRecentFiles:=True, _
    PasswordDocument:="", PasswordTemplate:="", WritePasswordDocument:="", _
    WritePasswordTemplate:="", Revert:=False, Format:=wdOpenFormatAuto, _
    Connection:= _
    "Provider=Microsoft.Jet.OLEDB.4.0;Password="""";Us er ID= <img src=/w3timages/censored.gif alt=censored border=0>;Data Source= <img src=/w3timages/censored.gif alt=censored border=0>;
    Mode=Read;Extended Properties="""";Jet OLEDB:System database="""";
    Jet OLEDB:Registry Path="""";Jet OLEDBatabase Password="""";Jet OLEDB:Engine Typ" _
    , SQLStatement:="SELECT * FROM `tblPrisons`", SQLStatement1:="", SubType _
    :=wdMergeSubTypeAccess
    ActiveDocument.MailMerge.DataSource.QueryString = _
    "SELECT * FROM `tblPrisons` WHERE `Date Received` = #date()# And `BRF` = 'y' " _
    & ""
    End Sub</pre>


    #date()# I just entered this thinking it would work, but it obviously doesn't. It originally had the date I built the macro and I have to reset it every time in my mail merge options in Word, but it stays the same in code. Any help would be appreciated.

  2. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Mail Merge with Current Date (Word 2003/SP 1)

    Try this:

    Format(Date, "mm/dd/yyyy")

    for 02/24/2006

    You might need to experiment with different layouts to match your Access field, although the SQL processor might be smart enough to figure out what you want.

    Also, if your field in Access stores a time other than midnight, you might need to use a "between" expression rather than an "equals" expression. We can cross that bridge if we come to it. <img src=/S/wink.gif border=0 alt=wink width=15 height=15>

  3. #3
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Moundridge, Kansas, USA
    Posts
    342
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Mail Merge with Current Date (Word 2003/SP 1)

    Ok, what am I doing wrong?

    ActiveDocument.MailMerge.DataSource.QueryString = _
    "SELECT * FROM `tblPrisons` WHERE `Date Received` = Format([Date Received], "mm/dd/yy") And `BRF` = 'y' " _
    & ""

  4. #4
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Mail Merge with Current Date (Word 2003/SP 1)

    Sorry, I thought you wanted to restrict the records you retrieve to records that have today's date. Thus my use of Date rather than a database field in my example. Is that what you wanted?

  5. #5
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Moundridge, Kansas, USA
    Posts
    342
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Mail Merge with Current Date (Word 2003/SP 1)

    Yes, I want to restrict the merged records to todays date. I tried inserting it like you had it but got all records returned.

    Do I leave the "`Date Received`" field out and insert Format(Date, "mm/dd/yyyy")? As I said, I am not at all proficient in code so some of this is strictly trial and error for me.

  6. #6
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Mail Merge with Current Date (Word 2003/SP 1)

    <P ID="edit" class=small>(Edited by jscher2000 on 24-Feb-06 14:54. )</P>It should work if you substitute Format(Date, "mm/dd/yyyy") for the date that was hardcoded in the macro when you recorded it. (Also remove any # symbols that were around the date.)

    Added: Sorry, you need ' (apostrophes) around the value, which would give you this:

    ActiveDocument.MailMerge.DataSource.QueryString = _
    <big>"SELECT * FROM `tblPrisons` WHERE `Date Received` = '" & Format(Date, "mm/dd/yy") & "' And `BRF` = 'y' " </big>_
    & ""

  7. #7
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Moundridge, Kansas, USA
    Posts
    342
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Mail Merge with Current Date (Word 2003/SP 1)

    This is the message I am getting.

    I have to leave work, I'll work on it next week!!
    Attached Files Attached Files

  8. #8
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Mail Merge with Current Date (Word 2003/SP 1)

    Please see the edit I had made to the above message. In general, any message you receive as an email notification should be checked on the board for updates, because the board does not mail updates. Sorry!

  9. #9
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Moundridge, Kansas, USA
    Posts
    342
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Mail Merge with Current Date (Word 2003/SP 1)

    Just back to work and did a paste & copy of the code you edited and now it works. I think I was missing one apostrophe.

    I will try to remember that about updates. Thanks for your help!

Posting Permissions

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