asked on

Data Type Mismatch in Criteria Expression

Hello,

I am trying to run an append Query in Access 2010 into a SQL Server 2008 R2 table and getting the error "Data Type Mismatch in Criteria Expression". As a result, the append query won't run. The join field are either CHAR or VARCHAR. These are not native access tables...I am just linking to these tables from Access.

I am attaching the Access generated SQL.

Any thoughts on this please?

Thank you
Access-Query-SQL.txt

SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Dale Fye

all you can really do is go to the source tables and check the data types and dproperties like allow Nulls and allow zero length strings

Anthony Perkins

In order to help you we need the schema (CREATE TABLE) for the following tables:
dbo_SY06000
dbo_Organizations
dbo_PM00200
dbo_Organizations_Ext

ASKER CERTIFIED SOLUTION

Jim Horn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

htamraz1

ASKER

I changed ' ' AS to '' AS; however, that alone did not resolve the issue.

I ended up having to removing the following two fields from the query/sql syntax and the append query worked fine. The two fields are:

'1900-01-01 00:00:00.000' AS EFTPrenoteDate, '1900-01-01 00:00:00.000' AS EFTTerminationDate

The database populated a default DATETIME value and having these two fields in query caused the error previously reported.

Your suggestions helped pin down the issue.