Solved

Datatype mismatch in criteria or expression error

Posted on 2014-01-03
6
376 Views
Last Modified: 2014-01-04
Experts, is the below sql how to handle Nulls?  

[import-CSM2].[Guarantee Code] and [c].[GuaranteeCode] are both number format if that matters and one has a space and other doesnt.   I suspect I am getting the error because either of those fields are Null.  I dont get a syntax just the "datatype mismatch in criteria or expression" error on run.

I am only needing to know if the below is the correct way to handle Nulls.  If so, then there's another issue, which I am not interested in knowing the solution to right now.  

here's a portion of the SQL WHERE:  (if need it all let me know)

 OR (((Exists (Select *
             from tblLetterOfCredit c
            where Nz([import-CSM2].[Guarantee Code],0) = Nz( [c].[GuaranteeCode],0)))=False))
0
Comment
Question by:pdvsa
[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
  • 4
6 Comments
 
LVL 28

Expert Comment

by:omgang
ID: 39755178
<< one has a space and the other doesn't >>
A space suggests a text/string value as opposed to a number value.  Trying to compare a string value to a number value should result in a datatype mismatch error.  NZ will only convert nulls, not empty strings.

Are you sure both fields/columns are number data type?
OM Gang
0
 

Author Comment

by:pdvsa
ID: 39755251
Yes, I am sure they are both number formats.
0
 

Author Comment

by:pdvsa
ID: 39755263
here are both the tables showing number format

NumberFormat
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

Author Comment

by:pdvsa
ID: 39756316
Hi, if you think that is the proper way to handle the nz please let me know.  I have seen different ways of handling and i assume the differences are in regards to the property

Thank you
0
 
LVL 38

Accepted Solution

by:
PatHartman earned 300 total points
ID: 39756605
Why would you not use a join?  It is simpler and more efficient (for Jet/ACE) than a subquery.

SELECT [import-CSM2].*, tblLetterOfCredit.*
FROM  [import-CSM2] Left Join tblLetterOfCredit ON [import-CSM2].[Guarantee Code] = [tblLetterOfCredit].[GuaranteeCode]
WHERE [tblLetterOfCredit].[GuaranteeCode] Is Null;

What about:

OR (Not Exists (Select *
             from tblLetterOfCredit c
            where Nz([import-CSM2].[Guarantee Code],0) = Nz([c].[GuaranteeCode],0)))

If you use Not Exists, you don't need to use the Nz() functions.    You needed them the way the expression was coded because -
null = anything results in null and that was what was probably causing the data type mismatch when compared to False.

In any event, I would suggest the left join as the better solution.
0
 

Author Closing Comment

by:pdvsa
ID: 39756899
thank you.  I will use your suggestion.
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
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…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

617 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