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
  • 4
  • 3
LVL 33

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.
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).
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.


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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Password on a button in Access 2013 7 35
Run Time Error 3075 15 45
combine ShipTo and BillTo Address 3 23
ms access 2013, running .mdb 2 31
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

896 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now