Fix Word Mail Merge Fails with Access Like 0 Query
Question details
A Word mail merge fails to recognize data records and reports them as empty when pulling data from an Access query that uses the criterion Like "0*".
- Product
- Microsoft Word
- Device & OS
- not provided
- Scenario
- Setting up an envelope mail merge in Microsoft Word using a Microsoft Access query as the data source.
- Observed behavior
- The mail merge process reports that the data records are empty despite the Access query successfully returning records. The issue disappears if the criterion is changed to Not Like "0*".
Ensure that your Microsoft Access database is saved and completely closed before initiating the mail merge in Word to prevent file-locking conflicts.
Adjust Access Query Criteria to Use an Exact Match
Modify the Access query to use a strict match instead of a wildcard, which bypasses the OLE DB wildcard compatibility issues between Word and Access.
Microsoft Word typically uses OLE DB to connect to Access databases. Because OLE DB uses a different SQL standard (ANSI-92) than native Access (ANSI-89), standard wildcards like '*' may not be recognized during the mail merge, resulting in empty data sets.
Launch Microsoft Access, right-click the query you are using for the mail merge, and select 'Design View'.
Locate the field using the 'Like "0*"' criterion. Change it to an exact match, such as 'Like "0"' or simply '"0"', if the data is only meant to contain the number zero.
Run the query to identify any entries that have unexpected characters after the '0'. Correct these entries in the underlying table so they match your new strict criterion.
Save the query, close Microsoft Access, and re-run your envelope mail merge in Microsoft Word.
Change the Wildcard to the OLE DB Standard
Replace the Access-specific asterisk wildcard (*) with the OLE DB percentage wildcard (%) so that Word correctly interprets the query.
Use DDE (Dynamic Data Exchange) for the Connection
Force Microsoft Word to use DDE instead of OLE DB to connect to Access, which preserves native Access queries and formatting.
Try WPS Office for Seamless Mail Merge Without Complex Queries
Avoid complex database query conflicts and OLE DB compatibility issues by using WPS Office. WPS Writer seamlessly integrates with WPS Spreadsheet for a straightforward, error-free mail merge experience. It is a highly compatible, lightweight, and completely free alternative to Microsoft Office.
- 1. Prepare Your Data in Spreadsheet: Open WPS Spreadsheet and organize your contact list or envelope data into clear columns, then save the file.
- 2. Start Mail Merge in WPS Writer: Open WPS Writer, navigate to the 'References' tab, and click the 'Mail Merge' button to activate the feature.
- 3. Connect Your Spreadsheet: Click 'Open Data Source' and select the spreadsheet you just saved to instantly connect your data without connection protocol errors.
- 4. Insert Fields and Merge: Click 'Insert Merge Field' to place your data into the document, then click 'Merge to New Document' to complete the process.
Frequently Asked Questions
Why does Word mail merge ignore my Access wildcard query?
Word connects to Access using an OLE DB provider by default. OLE DB uses a different SQL standard (ANSI-92) compared to native Access (ANSI-89). Because of this, native Access wildcards like the asterisk (*) are often unrecognized, causing the query to return zero records during the merge.
How can I make my Access query work with Word without changing connection methods?
You can change the wildcard in your Access query from an asterisk (*) to a percent sign (%). The percent sign is the correct wildcard for the OLE DB standard that Word uses, allowing the merge to read the filtered records properly.
Can I use an Excel spreadsheet instead of an Access database to avoid this error?
Yes. Exporting your Access query results to an Excel spreadsheet and using that Excel file as your Word mail merge data source is a very reliable workaround. Spreadsheets do not require complex query interpretation during the merge process.
