Solved

Datatype mismatch in criteria or expression error

Posted on 2014-01-03
6
373 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
  • 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

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 36

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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
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…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

828 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