Results 1 to 11 of 11
  1. #1
    2 Star Lounger
    Join Date
    May 2005
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Merge field date format (2007)

    I have automated access to create an email merge document. The table and query behind the merge field are both formatted as medium dates.

    When I open the word document from within access I get "2/6/2007 12:00:00 AM" at "12/30/1899 11:00:00 AM" The first grouping is supposed to be the date and the second the time
    Interestingly, if I directly open the word document it prompts for the same query but returns the date and the time appropriately. It doesn't matter if its a .doc or .docx extension

    Any ideas Any solutions?

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

    Re: Merge field date format (2007)

    I don't have Office 2007, so I have no idea whether this works the same as in Office XP and Office 2003.

    One option is to add formatting switches to the mail merge fields in Word.
    - Right-click the date merge field in the Word document and select Toggle Field Codes from the popup menu.
    - Add <code>@ "M/d/yyyy"</code> before the closing brace }
    - Press F9 to hide field codes and to update the field.
    - Repeat for the time field, but with <code> @ "H:mm AM/PM"</code>
    Of course, you can use other date and time formats.

    Another option might be to specify DDE as method to connect to the Access database - this should preserve formats defined in a query. But it is rather sluggish compared to other methods such as DAO, so I can't really recommend it.

  3. #3
    2 Star Lounger
    Join Date
    May 2005
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Merge field date format (2007)

    I have the following mergefields {MERGEFIELD "ApptDate"@ "M/d/yyyy" } at {MERGEFIELD "ApptTime" @ "H:mm AM/PM"} that returns 2/6/2007 at 12/30/1899 11:00:00 AM. I was unable to remove the date portion of the time expression.

    I still find it curious that it works fine if you open the word document but not if its opened from within access. Why should the formating be changed?

    nebbia

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

    Re: Merge field date format (2007)

    As I said, I don't have Office 2007 yet, so I have no idea.

  5. #5
    2 Star Lounger
    Join Date
    May 2005
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Merge field date format (2007)

    Do I have the formatting correct for the time field? As you can see the date is still part of the field results and I only want the time

    Thanks

  6. #6
    2 Star Lounger
    Join Date
    May 2005
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Merge field date format (2007)

    I'd like to try that but unfortunately I don't know where to use the format function. I've tried in the format area of the property sheet but I couldn't get it to work. Please instruct

    nebbia

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

    Re: Merge field date format (2007)

    H:mm AM/PM contains only time elements, so it shouldn't display a date.

    Another option would be to use the Format function in the Access query instead of the Format property. For example:

    ApptTime: Format([NameOfTable].[AppTime], "h:mm AM/PM")

    This makes ApptTime into a text string; you wouldn't need to format it in Word any more.

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

    Re: Merge field date format (2007)

    Open the query in design view.
    There should be a column with ApptTime as field. Change ApptTime to

    ApptTime: Format([NameOfTable].[AppTime], "h:mm AM/PM")

    where NameOfTable is the name of the table of which ApptTime is a field.

  9. #9
    2 Star Lounger
    Join Date
    May 2005
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Merge field date format (2007)

    I thought that we were there

    I tried ApptDate: Format([tblAppointments].[ApptDate],"mm/dd/yyyy") for the date field. This field has a date criteria. Substituting a date either with or without bracketing #""# does not help
    I tried copying the formating from the query (no criteria) and placing that formating date style as a criteria and it doesn't work either.

    What should have been simple has proven not to be

    nebbia

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

    Re: Merge field date format (2007)

    If you need date criteria, add ApptDate again, clear the Show check box for its column, and add the criteria to this column.

  11. #11
    2 Star Lounger
    Join Date
    May 2005
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Merge field date format (2007)

    Interesting trick not showing that field. I did try a second apptdate field with the criteria but neglected to uncheck show records.

    Curious situation but it appears to be working fien.

    As always thanks

    Nebbia

Posting Permissions

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