Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Data Type Mismatch in Criteria Expression

Posted on 2014-03-21
5
Medium Priority
?
17,319 Views
Last Modified: 2014-03-21
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
Comment
Question by:htamraz1
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 750 total points
ID: 39946623
try replacing ' ' with ''  or Null

i.e.,

' ' AS CUSTNMBR

with

'' AS CUSTNMBR

or

Null AS CUSTNMBR
0
 
LVL 48

Expert Comment

by:Dale Fye
ID: 39946644
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39946699
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
 
LVL 66

Accepted Solution

by:
Jim Horn earned 750 total points
ID: 39946701
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
 

Author Comment

by:htamraz1
ID: 39946820
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question