Datatype mismatch error

Posted on 2014-01-04
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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
LVL 34

Assisted Solution

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

Accepted Solution

mbizup earned 250 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.
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
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…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

630 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