Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Word / Mailmerge and Fax / February 2004

Tip: Looking for answers? Try searching our database.

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

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Mo - 26 Feb 2004 21:36 GMT
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 - 27 Feb 2004 12:53 GMT
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.

> Thanks, Mo
Mo - 27 Feb 2004 22:26 GMT
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

Rate this thread:






 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.