Solved

Datatype mismatch in criteria or expression error

Posted on 2014-01-03
6
369 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
Comment Utility
<< 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
Comment Utility
Yes, I am sure they are both number formats.
0
 

Author Comment

by:pdvsa
Comment Utility
here are both the tables showing number format

NumberFormat
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

Author Comment

by:pdvsa
Comment Utility
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 34

Accepted Solution

by:
PatHartman earned 300 total points
Comment Utility
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
Comment Utility
thank you.  I will use your suggestion.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
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…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

772 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now