Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 17485
  • Last Modified:

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
0
htamraz1
Asked:
htamraz1
2 Solutions
 
Rey Obrero (Capricorn1)Commented:
try replacing ' ' with ''  or Null

i.e.,

' ' AS CUSTNMBR

with

'' AS CUSTNMBR

or

Null AS CUSTNMBR
0
 
Dale FyeCommented:
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
0
 
Anthony PerkinsCommented:
In order to help you we need the schema (CREATE TABLE) for the following tables:
dbo_SY06000
dbo_Organizations
dbo_PM00200
dbo_Organizations_Ext
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Check the data types..Check EACH data type of both the source table, and the destination table that you're trying to insert records into, to make sure they are the same.

>' ' AS CBANKCD
>' ' AS CURNCYID
CD and ID normally implies a numeric value, and no numeric value will accept a space.
0
 
htamraz1Author Commented:
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.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now