Solved

Datatype mismatch error

Posted on 2014-01-04
8
289 Views
Last Modified: 2014-01-06
Experts,

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)
CTCS-882886

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
EE.accdb
0
Comment
Question by:pdvsa
  • 4
  • 3
8 Comments
 
LVL 33

Assisted Solution

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

Accepted Solution

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

You can try replacing your criteria with:

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

Author Comment

by:pdvsa
ID: 39757391
Imnorie:  
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.
0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 61

Expert Comment

by:mbizup
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:

SELECT * 
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).
0
 

Author Comment

by:pdvsa
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.
0
 
LVL 61

Expert Comment

by:mbizup
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)
0
 

Author Comment

by:pdvsa
ID: 39758265
Mbizup:  

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.

Error
0
 
LVL 61

Expert Comment

by:mbizup
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.
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Familiarize people with the process of utilizing SQL Server stored procedures 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 Micr…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

803 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