Solved

Datatype mismatch error

Posted on 2014-01-04
8
285 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
Comment Utility
In the query remove the join between the tables.
0
 
LVL 61

Accepted Solution

by:
mbizup earned 250 total points
Comment Utility
Its failing on nulls.  

You can try replacing your criteria with:

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

Author Comment

by:pdvsa
Comment Utility
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
 
LVL 61

Expert Comment

by:mbizup
Comment Utility
<< 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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

Author Comment

by:pdvsa
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Familiarize people with the process of utilizing SQL Server functions 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 Ac…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

743 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

17 Experts available now in Live!

Get 1:1 Help Now