Null dates in Access as '12:00:00 AM' in Word mailmerge

[frisasu]

How do I stop null dates in Access appearing as todays date or '12:00:00 AM'
when I do a Word mailmerge?

 

[macropod]

Hi frisasu,

You should be able to suppress the unwanted output via an IF test coded as:
{IF{MERGEFIELD AccDate \@ MMDDYYYY}<> "" {MERGEFIELD AccDate}}
or
{IF{MERGEFIELD AccDate \@ MMDDYYYY}<> "" {MERGEFIELD AccDate}}
where 'AccDate' is the name of your Access date field.

Note that the field brace pairs (ie '{ }') for the above examples are created via Ctrl-F9 - you can't simply type them or copy &
paste them from this message.

Also, you may want to add a formatting switch to the second 'AccDate' field. To get the date format you want, you can add a
formatting picture switch. In Word:
.. select the mergefield;
.. press Shift-F9 to expose the field coding. It should look something like {MERGEFIELD MyDate} where 'MyDate' is your mergefield's
name;
.. delete anything appearing after the mergefield's name and add '\@ "d MMMM yyyy"' to the field, as in {MERGEFIELD MyDate \@ "d MMMM
yyyy"}. With this switch your date will come out as '2 August 2008'. Other possible date formatting switches include:
. \@ "dddd, d MMMM yyyy";
. \@ "ddd, d MMMM yyyy";
. \@ "d MMM yyyy";
. \@ "dd/MMM/yyyy";
. \@ "d-MM-yy";
Note: you can swap the d, M, y expressions around, but you must use uppercase 'M's for months.
.. position the cursor anywhere in this field and press F9 to update it;
.. run your mailmerge.

 

[Peter Jamieson]

There are three main ways to try to deal with this:
a. Change the way Word connects to Access to DDE (or possibly ODBC)
b. Detect the specific case where the "date" is 12:00:00 AM and insert
blank instead, e.g.

{ IF "{ MERGEFIELD mydate }" = "12:00:00 AM" "" "{ MERGEFIELD mydate }" }

(where all the {} are the special field code braces you can insert using
ctrl-F9)
c. Define a query in Access with a new field that uses e.g. the format
function to format the date the way you want, and use that query as the
data source for your merge.

I find it difficult to recommend (a) - although it's probably the
quickest solution for a one-off merge, it's probably not a good long
term bet. You can change the connection method in Word 2003 and earlier
by checking Tools->Options->General->Confirm conversions at open, then
going through the connection process again and selecting DDE or ODBC.
(Or in Word 2007, look for the same option in Word Office Button->Word
Options). (b) is probably OK as long as none of your date/times is
/actually/ set to 12:00:00 AM. I'd opt for (c) if you are in a position
to create the query. (If not, you should also be able to issue the same
query as the SQLStatement parameter in a Word VBA OpenDataSource call

As far as I know, nothing you do in the Access field definition will
modify this behaviour.


Peter Jamieson

http://tips.pjmsn.me.uk