Word 2002 - Access Date Field will not merge as blank when it's empty

[Mo]

I have been trying to merge my Access Data into a Word 2002 document. My "If Statement" supplies a paragraph if the date field is empty. However, the date field will not come over empty even though there is no data in it. I tried taking it into Excel and merging from there. That was better. However, of the five date fields that I'm using in the merge, two of them still had data in the empty fields. It does not show up in either Access or in Excel, but appears when it is merged into Word.

In Access the field is formatted as a short date. In excel, the field that are blank show up formatted as general. Yesterday it was suggested I force the format in the Access query, but that did not help at all. This worked fine in Word 97. It appears to be something to do with Word 2002, but I can't figure out what

Thanks, Mo

 

[Peter Jamieson]

What has changed in Word 2002 is that the default method for connecting to
Access data has changed from DDE to OLEDB. With DDE, you generally get data
that is much closer to what you see in Access, but Word has to run Access to
do it. With OLEDB (ad the other connection method, ODBC), you generally get
the "underlying" data, and OLEDB generally ignores any format/layout
information you have specified in Access.

Typically, when you have blank dates in Access, you will soemthing like
"12:00:00 AM" or some such in Word if you connect using OLEDB.

You can revert to the DDE connection type by checking Word
Tools|Options|General|"Confirm conversion at open" then going through the
process to connect to your data source again, and choosing the DDE option
when it is offered. You may be able to deal with the problem using

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

(Make sure you compare with the actual value that Access returns - what I've
typed may not be correct, and use ctrl-F9 to insert each pair of {} )

Yesterday it was suggested I force the format in the Access query, but

that did not help at all

Just out of interest, how did you do that? Did you use the format()
function, and did you work directly with the SQL, or in the Query Design
grid?

--
Peter Jamieson - Word MVP
Word MVP web site http://word.mvps.org/

I have been trying to merge my Access Data into a Word 2002 document. My

"If Statement" supplies a paragraph if the date field is empty. However,
the date field will not come over empty even though there is no data in it.
I tried taking it into Excel and merging from there. That was better.
However, of the five date fields that I'm using in the merge, two of them
still had data in the empty fields. It does not show up in either Access or
in Excel, but appears when it is merged into Word.

In Access the field is formatted as a short date. In excel, the field

that are blank show up formatted as general. Yesterday it was suggested I
force the format in the Access query, but that did not help at all. This
worked fine in Word 97. It appears to be something to do with Word 2002,
but I can't figure out what.

 

[Mo]

To answer your question about forcing the format -- I did it in the Query Design grid by right clicking and going to properties and inserting the format in Format. But as I said, it gave me the same results

Thanks for your help. What you suggested is what I eventually came up with myself. Great minds think alike. Thanks again, Mo