The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.
One of a set of tools we're offering as a way of saying thank you for being a part of the community.
Solved
"Data type mismatch in criteria expression", just by JOIN'ing to another table
Posted on 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?
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?
-
2
- +1
5 Comments
Active 1 day ago
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.
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.
Active 1 day ago
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.
Does that reveal any blanks/nulls in the ParentID field? These may cause an error in your JOINs.
Active today
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!Chil
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
"WHERE DateValue(tblChildren!Chil
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
Featured Post
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
Join & Write a Comment Already a member? Login.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc.
In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
When we develop an application in Ms Access 2016 we should also try to protect the queries, macros and table links. I know I may not have a permanent solution but for novice users, they will not manage to break your application. Below is the detail …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Enter Foreign and Special Characters
Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~
Use these codes in any Windows application! ... whether it is a Micr…
Suggested Courses
Course of the Month3 days, 21 hours left to enroll
588 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.