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].[Guarante
[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].[Guarante
Last Comment
ASKER
Yes, I am sure they are both number formats.
ASKER
here are both the tables showing number format
ASKER
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
Thank you
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
thank you. I will use your suggestion.
Microsoft Access
Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.
226K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
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