Solved

Datatype mismatch in criteria or expression error

Posted on 2014-01-03
6
375 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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 

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 37

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

[Webinar] Code, Load, and Grow

Managing multiple websites, servers, applications, and security on a daily basis? Join us for a webinar on May 25th to learn how to simplify administration and management of virtual hosts for IT admins, create a secure environment, and deploy code more effectively and frequently.

Question has a verified solution.

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

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

734 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