• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 379
  • Last Modified:

Datatype mismatch in criteria or expression error

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
pdvsa
Asked:
pdvsa
  • 4
1 Solution
 
omgangCommented:
<< 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
 
pdvsaProject financeAuthor Commented:
Yes, I am sure they are both number formats.
0
 
pdvsaProject financeAuthor Commented:
here are both the tables showing number format

NumberFormat
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
pdvsaProject financeAuthor Commented:
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
 
PatHartmanCommented:
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
 
pdvsaProject financeAuthor Commented:
thank you.  I will use your suggestion.
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now