12:00:00 AM - Mail Merge field - Unable to use DDE workaround

Hello flakjack,

Well, if "" value is causing you trouble, I'd try to replace them all, for example:

If you have a table from A1 to C3, on D1 I'll enter in
C3:

Please Login or Register  to view this content.

And then do a Ctrl - H (Replace), type in "Delete" in the "Find What" box, leave the "Replace with" blank, hit replace all, and then you can do it the old way? xd

Originally Posted by flakjack

Can't believe these issues are still present in the latest Word version!

That's because you don't really understand how mailmerge works.

By default, Word 2002 & later use the OLE DB provider to get merge data from an Excel workbook. Because the OLE DB provider is designed to return data in a way that is compatible with Access and other relational database packages, it requires a specific data type for each field, and every value in that field must have only that data type. In Excel, however, cells in a column (field) can have different data types. When the OLE DB provider gets data from an Excel column with mixed data types, it has to determine the data type for each column. The first 8 records are used for that (the 8 can be changed in the Windows Registry, but it’s not advisable to do so). Records that don’t conform to the determined data type are liable to not be handled correctly. To complicate matters, there are two text data types: "text" (up to 255 characters) and "memo" (can be longer than that).

Some common mailmerge issues arising out of this include:
• Numbers but not text or dates being output for some records; and
• Text data being truncated at 255 characters.

Ideally, one would ensure each field has only one data type. Workarounds include:
• Inserting a dummy first record containing data in the format that is not being output correctly; or
• Reordering the data so the first record has content in the format that is not otherwise being output correctly.
Thus, if numbers appear but text and/or dates don’t, ensure the first record for that field has text or a date. Similarly, if text over 255 characters is being truncated, ensure the first record for that field contains more than 255 characters.

While you say:

certain merge fields are pulling from Excel into word as 12:00:00 AM, rather than the data stored on the table

You haven't said what they're supposed to show.

I had same issue. Things were fine until something unknown happened and Excel text field kept displaying 12:00am in Word. I tested with new blank Word documents with same results with same single text field from same Excel sheet. Oddly if I add another column in Excel sheet which references same text field (ex. new column H2=B2 where B2 is the text field in question), then the original text field works fine in Word without using the secondary and equal calc'd field value. But if that new calc'd field column is removed, the original text field again displays 12:00am in Word. So I then created new workbook and sheet, used the same Work document and problem went away but a different problem later arose where last text field in Word was truncated at 255 char. After more research, I learned what seems to be the correct way to link Excel 2016 or later to Work for a Mail Merge using DDE.

The fix for me was to strip out mail merge features from the Word document and to make Normal Word Document (under the Start Mail Merge ribbon drop down). I then closed, reopened and followed these steps:
1) When you connect to the Excel file via Use an Existing List selection under Select Recipients ribbon drop down a dialog will open titled Confirm Data Source and in my case with just one option (OLE DB Database Files) but don't choose that option.
2) Check Show All check box then select MS Excel Worksheets via DDE (*.xls), then click OK.
3) Eventually you will see another dialog box which asks if you want Named or Cell Range and the Entire Spreadsheet is already inputted for selection and say OK
Next the process will open your spreadsheet and if you flip back to Word, you will be able to hopefully be able to select and use fields without the two challenges I experienced.

Originally Posted by eskimopie

The fix for me was to strip out mail merge features from the Word document and to make Normal Word Document (under the Start Mail Merge ribbon drop down). I then closed, reopened and followed these steps:

Stripping out all the mailmerge features requires nothing more than answering 'No' to the SQL prompt when you open the mailmerge main document. You can then connect to whatever data source you want, with whatever connection method you prefer.

Using DDE as a workaround to what is clearly a problem with your source data is a kludge. My reply in post #4 sets out the correct way of handling such issues.