Fix a Data Type Mismatch in an Access Append Query
Question details
Identify and fix incompatible data types in an Access append query that uses NOT EXISTS to prevent duplicate records.

- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- An append query reads from a linked Excel table and checks the destination table for existing records before appending.
- Observed behavior
- The first batch appends successfully, but the query reports a data type mismatch when destination records exist and the NOT EXISTS condition is used.
Open the query in SQL View and identify the fields compared in its WHERE and NOT EXISTS clauses. Check the source and destination field types in Access, especially fields supplied by the linked Excel table.
Check the NOT EXISTS comparisons for incompatible field types
Use this method to isolate the comparison that fails when Access checks existing destination records.
A successful first append does not rule out an incompatible comparison. When the destination table is empty, Access may not need to evaluate a comparison against an existing destination row.
In Access, open the append query in Design View, then select View > SQL View. List each source-to-destination comparison in WHERE and NOT EXISTS, along with the fields being appended.
Open the destination table in Design View and check the Data Type and, for Number fields, Field Size of each compared field. Inspect the linked Excel table's field types in Access and look for comparisons such as text against a number or date.
Create a SELECT test query in SQL View using the same source, destination and comparison conditions. Test the conditions individually or add them back one at a time, then select Run to find which comparison triggers the error.
Adjust the source or destination field types so corresponding values are compatible. If conversion is necessary, validate the values first and use an appropriate Access conversion function in the test query; then run the append query only after the SELECT test returns the intended rows.

Import the Excel source into an Access staging table
Use a staging table when mixed Excel values or inferred linked-table types make the problem difficult to diagnose.
Review Excel Source Data with WPS Office
WPS Office is a free, lightweight alternative with a familiar interface for reviewing and editing Office files. Its Excel-compatible spreadsheet tools can help you inspect source data before importing it into Access. WPS does not fix Access queries or replace Access database functionality.

Frequently Asked Questions
Why does the append query work when the destination table is empty?
With no destination rows, Access may not evaluate a source-to-destination comparison inside NOT EXISTS. Once rows exist, an incompatible comparison can produce a data type mismatch.
Where can I check the fields used by NOT EXISTS?
Open the query in Access Design View and select View > SQL View. Inspect each comparison inside NOT EXISTS, then check the corresponding table fields and their data types.
Can linked Excel data cause a type mismatch?
Yes. Access infers types for linked Excel columns, and mixed or inconsistent values can lead to unexpected results. Importing the data into a staging table lets you inspect and validate the values before appending.
Should I convert a text identifier to a number?
Only if every value is a valid number and formatting is not meaningful. If identifiers contain leading zeros, use compatible text fields for the comparison instead.




