Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Nov 2002
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Mail Merge-Need 'Blank' Date Value-Access to Word (Office 2002)

    Hi! I'm new, but I've been reading for a long time. Just want to say thanks to everyone for saving me untold aggravation and wasted time with the great solutions posted here.

    I can't find the answer to this one. It's simple but maddening. I just converted from Office 97 to Office 2002. I had a simple, workable mail merge system in Office 97 between Word and Access. I used it for many chores, i.e., supply address blocks, create case deadline charts, make labels and address lists, etc.

    The upgrade to 2002 has been frustrating but workable, except for one problem. One of my Word merge docs is a "Case Status Chart" that contains many date fields in a Word table. Some of the date fields in Access are blank. That's OK. In Office 97, they merged as blank into Word. That was good.

    In Office 2002, some date fields are still blank in Access, BUT Word populates them with a default value. When I click on "Mail Merge Recipients" (the place in Word 2002 where you view and sort your Access data), all the blank Access date fields show "12:00:00 AM." I've searched in vain in both programs for some simple way to get rid of this.

    Can anyone help? I'm OK for an amateur, but I don't do VBA - I need to find the "place" in one of the programs if possible. If the solution is a Word mergefield, can you type an example and explain where to put it, in relation to the Access field? Thanks a lot!

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

    Re: Mail Merge-Need 'Blank' Date Value-Access to Word (Office 2002)

    I had this problem in Access 97/Word 97 too! The cause is that dates/times are stored in Access as numbers; Word interprets a null value as 0, which corresponds to 12/31/1899 12:00:00 AM. I solved it by using a query in Access as data source, and replacing all date/time fields by calculated string fields that format the date/time the way I want - that way, a null value becomes an empty string. Example:
    BirthDate: Format([myTable].[BirthDate],"Long Date")
    The name of the table is specified explicitly to avoid circular references.

  3. #3
    New Lounger
    Join Date
    Nov 2002
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Thanks!

    Thanks for the prompt reply! I wonder why you had this issue in Word 97, and I didn't. Formatting luck, I guess. Honestly, one would think it would be fixed by Word 2002.

    I appreciate your workaround proposal, it sounds perfectly logical. I already have a query set up in Access. However, I've never done a calculated string field! I did try your string in the query's Design View, but I am just not advanced enough, I got lost when it asked me for the Parameter Value.

    A low-tech solution, annoying but effective, is to just do the merge, then "Search-Replace All" the offending inserts with blanks.

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

    Re: Thanks!

    If you are prompted for a parameter, Access doesn't recognize a name as a valid field name (or table name), so perhaps you misspelled something. Of course, you must use the actual field and table names of your table, not the names used in my example.

    I have attached a screen shot of a query with a calculated string field based on a date. Note how the exact table and field name are used.
    (In the screen shot, the separator in the Format function is a semi-colon instead of a comma. This is due to my Regional Settings.)
    Attached Images Attached Images
    • File Type: gif x.gif (3.2 KB, 0 views)

  5. #5
    New Lounger
    Join Date
    Nov 2002
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    That Works

    Hans, it works! Thank you so much. A picture's worth a thousand words! <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

Posting Permissions

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