Results 1 to 14 of 14
  1. #1
    dehdahdoh is offline Novice
    Windows Vista Access 2007
    Join Date
    Apr 2013
    Posts
    8

    Data Type Mismatch in Criteria Expression

    I am not new to using databases however I am new to fixing or managing them. We have databases that are periodically reset. The reason being that we produce publications for our customers and each time we produce those publications we have a new set of entries.



    I reset a particular database, input new data, run a couple of reports. Yesterday when I went to run a report based on a query and sub-form I got a dialog box with an error; "Data Type Mismatch in Criteria Expression". We have five tables that are linked via relationships. The relationships are correct between these five tables. I have spent about 12 hours looking for an answer in the database and on line. Part of my problem, I have no idea or knowledge of code so most of the answers were too far above my head.

    I have compared the table fields to make sure that they are all the same, I have gone into the properties for each table and line to make sure all is set up correctly. I have no idea what is left to look at. I deleted the relationships and re-joined the tables and have discovered two that I cannot join if I want to maintain referential integrity. They are joined on the order field. I have attached snips of each of the tables if that would help.
    Attached Thumbnails Attached Thumbnails Capture-Order Table.jpg   Capture-Dog Table.JPG  

  2. #2
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Look at the query that the report is based on. That error message seems to imply an issue with the Criteria in your query. The "data mismatch" usually implies that you are comparing things of different data types, i.e. if you data field was numeric and your criteria was something like ="A". Sometimes, null or blank field values for certain records can cause issues.

    If you have multiple criteria in your query, to locate which one is causing the issue, you can try removing them one at a time until you no longer get errors. Then you know that the last one you removed is the offending one.

  3. #3
    dehdahdoh is offline Novice
    Windows Vista Access 2007
    Join Date
    Apr 2013
    Posts
    8
    Thank you for your help, now I am getting a different and I am not going to pretend that I know how to fix it. Th error is "Syntax error in FROM clause." Below is the SQL that is associated with the Query.

    SELECT Dog.Order, Dog.Sex, Dog.[Owner 1], Dog.[Owner 2], Dog.Address, Dog.City, Dog.State, Dog.Zip, Dog.Date, [Entry - Classes].[Reg#], Dog.Breed, [Entry - Classes].Class, [Entry - Classes].Order, Dog.Jump, Dog.[OB Jump], [Entry - Classes].[Armbd#], [Entry - Classes].Club, [Entry - Classes].Entr, Dog.Title, Dog.[Dogs Name], Dog.[S-Title], [Entry - Classes].Show1, [Entry - Classes].Show2, [Entry - Classes].Show3, [Entry - Classes].Show4, [Entry - Classes].Entry_Fee, [Entry - Classes].Fax_Fee, [Entry - Classes].[$Rec'd], [Entry - Classes].Balance
    FROM Dog INNER JOIN [Entry - Classes] ON Dog.[Reg#] = [Entry - Classes].[Reg#], Dog INNERJOIN [Entry - Classes] IN Dog.[Order]=[Entry Classes].[Order}
    WHERE ((([Entry - Classes].Class)>="01" And ([Entry - Classes].Class)<="90"))
    ORDER BY Dog.Order;

  4. #4
    dehdahdoh is offline Novice
    Windows Vista Access 2007
    Join Date
    Apr 2013
    Posts
    8
    Quote Originally Posted by dehdahdoh View Post
    Thank you for your help, now I am getting a different and I am not going to pretend that I know how to fix it. Th error is "Syntax error in FROM clause." Below is the SQL that is associated with the Query.

    SELECT Dog.Order, Dog.Sex, Dog.[Owner 1], Dog.[Owner 2], Dog.Address, Dog.City, Dog.State, Dog.Zip, Dog.Date, [Entry - Classes].[Reg#], Dog.Breed, [Entry - Classes].Class, [Entry - Classes].Order, Dog.Jump, Dog.[OB Jump], [Entry - Classes].[Armbd#], [Entry - Classes].Club, [Entry - Classes].Entr, Dog.Title, Dog.[Dogs Name], Dog.[S-Title], [Entry - Classes].Show1, [Entry - Classes].Show2, [Entry - Classes].Show3, [Entry - Classes].Show4, [Entry - Classes].Entry_Fee, [Entry - Classes].Fax_Fee, [Entry - Classes].[$Rec'd], [Entry - Classes].Balance
    FROM Dog INNER JOIN [Entry - Classes] ON Dog.[Reg#] = [Entry - Classes].[Reg#], FROM Dog INNERJOIN [Entry - Classes] ON Dog.[Order]=[Entry Classes].[Order]
    WHERE ((([Entry - Classes].Class)>="01" And ([Entry - Classes].Class)<="90"))
    ORDER BY Dog.Order;
    I added the verbage in red. This is the item that was giving me the errors in the criteria expression from first post. As soon as I got the correct query attached to the report I began geting this error when I attempt to run the query; Syntax error in FROM clause.

  5. #5
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    INNER JOIN is two words, not one (not INNERJOIN).

  6. #6
    dehdahdoh is offline Novice
    Windows Vista Access 2007
    Join Date
    Apr 2013
    Posts
    8
    I have not been able to get this problem resolved. So I have started over. I have resolved most of the problems that I was having previously. Now I am attempting to do an append query to combine the information of two tables in the same database, Dog Table and Springer Spaniel Dog Table. When running the query, I continually am getting an error "Microsoft Office Access can't append all the records in the append query" At first I got the 242 records (the entire Springer table) due to validation rule violations. I have corrected that problem and now I am getting 202 records to the table due to key violations. Just this morning I have spent 3 hours on this. I have had both tables open and have gone line by line to make sure the fields were identical for both tables. I manually went through the Springer table to search for duplicates and ran the duplicates query. Ia m at a loss as to where to go next. I have attached the error and shots of the tables. I am desperate and have to have this resolved by end of day today. Thank yo for your help with thisClick image for larger version. 

Name:	AppendQueryErrorMsg.JPG 
Views:	6 
Size:	47.1 KB 
ID:	12143Click image for larger version. 

Name:	AppendQuery.JPG 
Views:	8 
Size:	59.0 KB 
ID:	12144Click image for larger version. 

Name:	AppendQueryTables-Dog-SpringerSpanielDog.JPG 
Views:	7 
Size:	179.7 KB 
ID:	12145

  7. #7
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Look at your Reg# field. It requires unique entries. Do both tables have unique entries, not only within themselves, but when compared to each other?
    Also, I would highly recommend changing your field name. Using special characters like # in object/field names is not a good idea and could lead to issues.

  8. #8
    dehdahdoh is offline Novice
    Windows Vista Access 2007
    Join Date
    Apr 2013
    Posts
    8
    Quote Originally Posted by JoeM View Post
    Look at your Reg# field. It requires unique entries. Do both tables have unique entries, not only within themselves, but when compared to each other? The third picture I included the side by side of both tables-RegNo. That would be the side by side. What do you mean buy "Do both tables have unique entries, not only within themselves..." I did go through and pull all of the duplicates out of the Springer Table. DO I need to take a look at teh dog table for duplicates as well? When reading the help in Access it stated that I needed to do it in only the table that I was merging (Springer Spaniel Dog) into the other one (Dog)

    Also, I would highly recommend changing your field name. Using special characters like # in object/field names is not a good idea and could lead to issues.
    I changed this - thank you for mentioning it.

  9. #9
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Which table are you appending to the other?

    Since that Reg field has to be unique, you need to compare the records from the table that you are appending to the the table you are appending it to. If any of the values already exist in that table, those records will not be added.

    It might be helpful if you post the SQL code of your Append query, and the contents of these tables, at least the Reg field (or better yet, upload the whole database).

  10. #10
    dehdahdoh is offline Novice
    Windows Vista Access 2007
    Join Date
    Apr 2013
    Posts
    8
    I am bringing the dogs from the Springer Spaniel Table into the Dog Table. I have no idea where to find the SQL code. I will upload the database. Well the database it too large to upload. I will see if I can find the SQL statement.

    INSERT INTO Dog
    SELECT [Springer Spaniel Dog].*
    FROM [Springer Spaniel Dog];

    None of the numbers can possibly be in both tables. The Reg No is issued by the American Kennel Club and these are different breed of dogs. I have done a find duplicates query for both tables and manually gone through each of them.

    The contents of the tables...? Do you mean all of dogs in the table?

  11. #11
    dehdahdoh is offline Novice
    Windows Vista Access 2007
    Join Date
    Apr 2013
    Posts
    8
    I have discovered how to Zip a file in Windows7 so here is the database.TCVESSA 13 - zip file.zip

  12. #12
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    I'll see if I can download your database tonight when I am at a place where I can download files and take a look at it.

  13. #13
    Beetle is offline Unrelatable
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    Camp Swampy (Denver, CO)
    Posts
    207
    The problem has nothing to do with the Primary Key field (RegNo) of the two tables involved in the append query. The problem is that you have Referential Integrity enforced on the relationship between the Order table and the Dog table based on the Order field. I'm not sure how RI got enforced in the first place here because the existing data violates RI rules (you have no records in the Order table, but you have multiple records in the Dog table). The append query will not run because the records you are trying to insert contain no value in the Order field and therefore violate RI rules. If you remove RI the query will run, but be advised that once you remove RI you likely will not be able to reestablish it.

  14. #14
    dehdahdoh is offline Novice
    Windows Vista Access 2007
    Join Date
    Apr 2013
    Posts
    8
    Thank you for all of your help! Unfortunately you are right and I can't reestablish RI. The RI rules must become more stringent with newer versions of Access. We make this relationship all the time. We have to annually reset the databases. We take out the relationships, delete all the info except for the dogs in the dog table. However we do remove the information from the Order field as it is different the following year. Then we re-establish the relationships with RI. It has worked this way for years. Go figure! Thank you again for all of your help you have no idea how much I appreciate it!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Data Type mismatch in criteria expression
    By elb1999 in forum Queries
    Replies: 2
    Last Post: 01-20-2012, 02:38 PM
  2. Data type mismatch in criteria expression
    By buienxg in forum Access
    Replies: 2
    Last Post: 11-22-2011, 10:29 AM
  3. Data type mismatch in criteria expression
    By Douglasrac in forum Forms
    Replies: 3
    Last Post: 11-23-2010, 10:46 AM
  4. data type mismatch in criteria expression
    By broecher in forum Access
    Replies: 4
    Last Post: 09-10-2010, 08:29 AM
  5. Data type mismatch in criteria expression
    By shexe in forum Queries
    Replies: 2
    Last Post: 09-01-2010, 12:47 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums