Results 1 to 9 of 9
  1. #1
    4 Star Lounger
    Join Date
    Mar 2005
    Location
    Leicestershire, United Kingdom
    Posts
    506
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Date formatting on mail merge? (2000)

    Long time away from this projec, but have a deadline of before christmas now [img]/forums/images/smilies/sad.gif[/img] Looking for some assistance.

    I have a date field on a mail merge for an appointment date which is entered via calendar popup in access. We then merge the current record into a word document to print a letter. The date i would like to come out like 8th December 2005 instead of 8 December 2005, is there a way of achieveing this?

    Luke
    Best Regards,

    Luke

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

    Re: Date formatting on mail merge? (2000)

    Perhaps <!post=Date Calculations in Word,249902>Date Calculations in Word<!/post> helps.

  3. #3
    4 Star Lounger
    Join Date
    Mar 2005
    Location
    Leicestershire, United Kingdom
    Posts
    506
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date formatting on mail merge? (2000)

    When the the time goes into word, it always put the date 30/12/1899 in front of it, ay ideas?
    Best Regards,

    Luke

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

    Re: Date formatting on mail merge? (2000)

    A dates and times are both stored in Windows with 1 day as unit, and December 30, 1899 is "day 0". If there is only a time component, the date part is 0, hence the date you see. You can right-click a merge field and select Toggle Field Codes from the popup menu. You'll see something like

    { MERGEFIELD TimeField * MERGEFORMAT }

    You can add a format switch, for example

    { MERGEFIELD TimeField @ "h:mm am/pm" * MERGEFORMAT }

    to display the time only. Press F9 to hide field codes and update the field.

  5. #5
    4 Star Lounger
    Join Date
    Mar 2005
    Location
    Leicestershire, United Kingdom
    Posts
    506
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date formatting on mail merge? (2000)

    Putting that when I toggle the field does not make a difference it is still there on the word document.
    Best Regards,

    Luke

  6. #6
    4 Star Lounger
    Join Date
    Mar 2005
    Location
    Leicestershire, United Kingdom
    Posts
    506
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date formatting on mail merge? (2000)

    The command button gets the current record and put that to a query. I check the query and the time field is set at 10:30 then the query exports to a txt file which is then merge into the word document. On the text file I feel the 30/12/1899 10:30:00 which is obviously being bpassed to word.

    I adjust the field codes as prescribed in an earlier e-mail and that has no effect whatsoever.

    any ideas?
    Best Regards,

    Luke

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

    Re: Date formatting on mail merge? (2000)

    Have you tried pressing F9 (while the field is selected)?

  8. #8
    4 Star Lounger
    Join Date
    Mar 2005
    Location
    Leicestershire, United Kingdom
    Posts
    506
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date formatting on mail merge? (2000)

    In word when you see <<TimeField>> and press F9 it skips to the end of the >>. If you toggle the field and then press is it adds <<TimeField>> onto the end.
    Best Regards,

    Luke

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

    Re: Date formatting on mail merge? (2000)

    A text file just contains, well, text. So Word doesn't know that one of the fields is a date/time field, and hence it can't format it.
    Instead of exporting to a text file, export to Excel.
    You could also initiate the merge in code from Access and pass the appropriate SQL string to act as data source. You wouldn't need to export a data file at all.

Posting Permissions

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