Results 1 to 9 of 9
  1. #1
    New Lounger
    Join Date
    Jan 2003
    Location
    Ocean, New Jersey, USA
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Mail Merge Date/Time Field Format (Access XP/Word XP)

    Can someone help me format a mail merge field that contains a time value? I've tried formatting the (Access) source field as a Short Time or a Medium Time. Tried formatting the (Word) merge field with {Mergefield "timefieldname" @ "hh:mm AM/PM"}. So far no luck. It always comes out: 1899-12-30 12:30:00 (for a 12:30 PM time).
    {Mergefield "datefieldname" @ "ddd, MMM d, yyyy"} works OK for a date field. What am I missing for the time formatting? Thanks alot.

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

    Re: Mail Merge Date/Time Field Format (Access XP/Word XP)

    I don't know what causes your problem - formatting a time field the way you do works fine for me (Word and Access 2002 SP-2 under Windows XP Pro SP-1).

    As a work-around, you can use the Format function in Access. If the data source of the mail merge is a table, create a query based on that table and add the fields you need for the mail merge to the query grid. If the data source is a query, use that. Add a calculated field

    <font face="Georgia">FormattedTime:Format([TimeFieldName],"hh:mm AM/PM")</font face=georgia>

    Note: FormattedTime will be a text field; it can't be used (directly) for time calculations.

    If the data source of the mail merge document is a table now, change it to the query.
    Remove the merge field based on TimeFieldName and replace it by a merge field based on FormattedTime. There is no need to format it - the formatting has been done in Access.

    Of course, you must substitute the actual field name for TimeFieldName.

  3. #3
    New Lounger
    Join Date
    Jan 2003
    Location
    Ocean, New Jersey, USA
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mail Merge Date/Time Field Format (Access XP/Word XP)

    Thanks! That works. Had no idea formatting cpability exits on the "build" line other than the built-in functions. How do you find out about such things? Can you recommend a good resource...or is it OK to keep bothering you :-)
    Thanks again.

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

    Re: Mail Merge Date/Time Field Format (Access XP/Word XP)

    There are several good books about Access and Access VBA. Since the differences between Access 2000 and Access 2002 are relatively small, a book on one of these versions will do for the other too. To get a good introduction to Access, take "Microsoft Access 2000 (or 2002) Step By Step". For reference, use the "Access 2000Developer's Handbook" (2 volumes) - not cheap, and not suitable to learn VBA from, but an inexhaustible source of information.
    Personally, I learned a lot from browsing around the Access 97 online help; sadly, the online help for Access 2002 is harder to use.
    Another good source of information are the various demo databases provided by Microsoft. Northwind comes with Office Pro; there are sample Queries, Forms and Reports databases for Access 2000, as well as the Developer Solutions and Orders databases that build on the Northwind database.
    Then, there is Dev Ashish's Access Web on the MVPS site, and there are many others.
    Last but not least, Woody's Lounge is a wonderful source of information; try the Search option. And there are helpful Loungers online 24 hours a day...

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Mail Merge Date/Time Field Format (Access XP/Word XP)

    In addition to the books Hans mentioned, I like Alison Balter's Mastering Access 2002 (also two volumes) which is much more tutorial than the Developer's Handbook, and aimed at the novice to intermediate developer. It also contains lots of practical knowledge and recommendations.
    Wendell

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Wetherby, Yorkshire, England
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mail Merge Date/Time Field Format (Access XP/Word XP)

    I had the same problem. I formatted the date in Access and then transferred it as a text string and have had no problems since. There is a Microsoft knowledgebase item covering it but I can't remember the link.
    Peter

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

    Re: Mail Merge Date/Time Field Format (Access XP/Word XP)

    The MSKB article that comes nearest is ACC2000: How to Use a Query to Format Data for a Mail Merge.

  8. #8
    New Lounger
    Join Date
    Jan 2003
    Location
    Ocean, New Jersey, USA
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mail Merge Date/Time Field Format (Access XP/Word XP)

    Thank you one and all for your helpful suggestions.

  9. #9
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Wetherby, Yorkshire, England
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mail Merge Date/Time Field Format (Access XP/Word XP)

    No - it was this one that I was thinking about. Of course, I see now that it's for the older versions (I don't have any XPs). The ODBC part might still apply though.

    MSKB Q220547 WD2000: Date incorrect using ODBC ...

    Peter

Posting Permissions

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