Solved

"Data type mismatch in criteria expression", just by JOIN'ing to another table

Posted on 2013-05-14
5
612 Views
Last Modified: 2013-05-14
Hello, I have two tables, say "tblParents" and "tblChildren" (Access 2010). They both have primary keys that are AutoNumber fields (say "ParentKey" and "ChildKey"). tblChildren carries ParentKey field as a Number, Long Integer.

There is a Relationship defined that LEFT JOIN's tblParents!ParentKey to tblChildren!ParentKey, as One-To-Many, all 3 options checked for Ref Integrity, Cascading Updates & Deletes.

I can start with a query with just tblChildren, printing most of the fields, for all records. No problems. But when I add tblParents to the query, and the JOIN is automatically created from the Relationship, I get the "Data type mismatch in criteria expression" error message, even if I don't throw down any fields from tblParents.

I have checked for missing or bad values in the key fields in both tables, and checked for orphans in tblChildren. Everything seems ok.

Any ideas?
0
Comment
Question by:mlagrange
5 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 39164498
We need to see the sql of the problem query.
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 250 total points
ID: 39164500
have you tried doing Compact and repair?
File > manage >compact and repair
0
 
LVL 61

Assisted Solution

by:mbizup
mbizup earned 250 total points
ID: 39164505
Did you post the exact syntax you have tried in your query?

You need to use periods, not exclamation marks:

..... LEFT JOIN ....  ON tblParents.ParentKey = tblChildren.ParentKey

If that is not the problem, then do you have any corrupted records in either table where the PK field might have been messed up?

Try Compact/Repairing your database.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39164568
Also, for kicks - try sorting tblChildren by ParentID.

Does that reveal any blanks/nulls in the ParentID field?  These may cause an error in your JOINs.
0
 

Author Comment

by:mlagrange
ID: 39165188
ok, I was filtering on a date field in tblChildren, which I was wrapping with DateValue() to get rid of the time component on some records, and that worked fine, until I JOIN'ed to tblParents, which had some parent records with no children, as it turns out...

"WHERE DateValue(tblChildren!ChildDate) <= #04/30/2013#  "

The filtering on the expression that assumed a value in the date field was what was causing the error message, when it hit the nulls.

I reversed the lay-out of the query, LEFT JOIN'ing  tblChildren to tblParents, and that side-steps the "childless" parent records.

Thank you for your responses
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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…

685 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