Go Premium for a chance to win a PS4. Enter to Win


Datatype mismatch error

Posted on 2014-01-04
Medium Priority
Last Modified: 2014-01-06

I have uploaded a file with a query.  
It uses a function that strips out characters:  leading and trailing 0's, dashes "-" and slashes "/" and a spaces " "

The query should return what is NOT IN importCSM-2 comparing Reference Numbers between the two tables.

In the tables, I have one record in each:
00CTCS-88288600 (notice the leading and trailing zero's)

If I run the query, I get the error and I dont know why.  I also dont know if I am using the StrippedChar function correctly.  The query should not return a record as the fucntion should strip the leading and trailing zeros leaving only CTCS-882886.  

Please see attached.  It is pared down to only 2 tables, 1 query and 1 record in both tables.

thank you
Question by:pdvsa
  • 4
  • 3
LVL 35

Assisted Solution

Norie earned 1000 total points
ID: 39756927
In the query remove the join between the tables.
LVL 61

Accepted Solution

mbizup earned 1000 total points
ID: 39756929
Its failing on nulls.  

You can try replacing your criteria with:

StrippedChar([import-CSM2].[Reference Number] & "")

Author Comment

ID: 39757391
thanks for the response.  Deleteing the reference did remove the datatype mismatch error.  I dont completely understand why though.  

Mbizup:  The null handler worked too even with the relationship.  

Are both of these Null handlers the same (I put Nz in the second one)?
StrippedChar([import-CSM2].[Reference Number] & "")  
StrippedChar(Nz([import-CSM2].[Reference Number],""))
==> first method is less characters only, correct?  they both work

This criteria:  Not In ([import-csm2].[Reference Number])
==>was not needed at all.  I deleted it & qry works how I need it too.  
Question:  Regarding the "Not In" above, is the StrippedChar updating the [Reference Number]? Meaning that the characters in [import-csm2].[Reference Number] are stripped?  If so, then there is a sort of duplication and this is why I can delete the "Not In" criteria in that column and left with the WHERE as below (bold).

REmoving the Not In criteria I have the WHERE condition:
SELECT [Import-jpm].[JPM REF NUMBER], StrippedChar([jpm ref number]) AS Expr1
FROM [Import-jpm], [import-csm2]
WHERE (((StrippedChar([jpm ref number]))<>StrippedChar([import-CSM2].[Reference Number])));

So what I understand is that I could have either:
1) Deleted the relationship (and wouldnt need any handler for Nulls)
2) Keep the reference but would need the handler for Nulls

Is deleting the relationship sort of a handler for Nulls?  Maybe in this certain situation it is.

fyi:  I did need the NOT EQUAL (<>) in the WHERE condition.  I figured that out!  yippee..

appreciate your comments to my questions.  It is at the heart of why I have issues with these queries and the Mismatch error.
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

LVL 61

Expert Comment

ID: 39757793
<< Are both of these Null handlers the same (I put Nz in the second one)? >>

Yes - they both replace NULLs with the empty string ("").

<<This criteria:  Not In ([import-csm2].[Reference Number])
==>was not needed at all.  >>

That condition is actually more than not needed.  I think it was what was causing your JOIN to fail.  The NOT IN condition is basically excluding the very same records that you are trying to JOIN on.

How did the query(s) work out with more than one record in each table?

The LEFT JOIN you had started out with was actually a common approach to Unmatched queries (finding records in one table that do not exist in another).

The basic syntax is:

FROM TableA LEFT JOIN TableB ON TableA.SomeField = TableB.SomeField 
WHERE TableB.SomeField IS NULL

Open in new window

Since you need to compare a field to the return from a function on a field in the other table, in your case the syntax for an unmatched would be:

SELECT [Import-jpm].[JPM REF NUMBER], StrippedChar("" & [jpm ref number]) AS Expr1
FROM [Import-jpm] LEFT JOIN [import-csm2] ON [Import-jpm].[JPM REF NUMBER] = StrippedChar("" & [import-csm2].[Reference Number])
WHERE [Guarantee Code] IS NULL

Open in new window

<< Is deleting the relationship sort of a handler for Nulls?  Maybe in this certain situation it is.  >>
This works in this situation, but it is not really a Null handler.  IF you have nulls for [jpm ref number] in your data, you would have to use NZ or concatenate an empty string (""& [jpm ref number]).

The reason for this is that the function StrippedChar accepts a *string* as its passed parameter, and NULLs are not strings (resulting in type mismatch or invalid use of null errors).  So you either need to ensure the passed parameter is a string (both null handlers do this), or change the data type of the parameter in the function to Variant (which accepts nulls).

Author Comment

ID: 39758021
mbizup:  thank you.  

<How did the query(s) work out with more than one record in each table?
It seeems I need to have the LEFT JOIN relationship
like this:
FROM [Import-jpm] LEFT JOIN [import-csm2] ON [Import-jpm].[JPM REF NUMBER] = [import-csm2].[Reference Number]

If I delete the relationship then the query returns a myriad of records.  This is if I run the query in my db with a lot of records.  

So it seems I need the relationship if working with a larger dataset?
that really adds a level of complexity to it.

thank you for the great help.
LVL 61

Expert Comment

ID: 39758225
Yes - that single record is a special case which just happens to work with the <> criteria.

NOT IN and <> are different beasts and what you want is NOT IN.

However NOT IN is notoriously slow, and LEFT or RIGHT joins are generally better.

The LEFT (or RIGHT) join syntax does a couple of things:

1.  It looks for records where the fields or expressions are equal

This may seem backwards whan what you need are unmatched records, but

2.  It includes all records from one of the tables, and only matched records from the other.

3.  It shows NULLS in all fields from the second table where matches to the record in the first table could not be found.

So this general syntax again works:

SELECT [Import-jpm].[JPM REF NUMBER], StrippedChar("" & [jpm ref number]) AS Expr1
FROM [Import-jpm] LEFT JOIN [import-csm2] ON [Import-jpm].[JPM REF NUMBER] = StrippedChar("" & [import-csm2].[Reference Number])
WHERE [Guarantee Code] IS NULL

Open in new window

ie: do a directional JOIN and isolate the records where NULLS are found in key field(s)

Author Comment

ID: 39758265

You need the [Guarantee Code] is NULL because it is a key field?   This is very hard to follow because to me it seems there is different solution for RIGHT or LEFT joins.  

Do you know why do I get this error?  It executes but gives the error when hitting design.

LVL 61

Expert Comment

ID: 39758285
The function StrippedChar is not representable in design view when used in a JOIN - so you're working with SQL and datasheet view only.

It's more of an informational message than an error.  UNION queries similarly cannot be represented in design view.

Featured Post

[Webinar] Cloud Security

In this webinar you will learn:

-Why existing firewall and DMZ architectures are not suited for securing cloud applications
-How to make your enterprise “Cloud Ready”, and fix your aging DMZ architecture
-How to transform your enterprise and become a Cloud Enabler

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Suggested Courses

971 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