Avatar of pdvsa
pdvsaFlag for United States of America asked on

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))
Microsoft Access

Avatar of undefined
Last Comment
pdvsa

8/22/2022 - Mon
omgang

<< 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
ASKER
pdvsa

Yes, I am sure they are both number formats.
ASKER
pdvsa

here are both the tables showing number format

NumberFormat
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
ASKER
pdvsa

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
ASKER CERTIFIED SOLUTION
PatHartman

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
pdvsa

thank you.  I will use your suggestion.