Results 1 to 15 of 15
  1. #1
    greatwhite is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Mar 2019
    Location
    Ottawa, ON
    Posts
    56

    Type Conversion Error

    I am trying to append (insert data into a new table) that the data is being ported to. All the other data went fine for the other tables. With this SQL statement I am getting a type conversion error and cannot see why. The data also looks correct and the tables have the same column settings. Maybe someone can spot the error. I have included a pic of Tables and the error.


    INSERT INTO tblEquipPanelBreaker ( keyEquip, numBreakerPosition, numBreakerDetailRow, strBreakerSize, optA103, strA103, keyCableDn, strPhase1, strPhase2 )

    SELECT epb.keyEquip+10000000, epb.numBreakerPosition
    , epb.numBreakerDetailRow, epb.strBreakerSize
    , epb.optA103, IIF(epb.optA103 = 1, 'Y', 'N'), epb.keyCableDn+10000000


    , epb.[Phase 1], epb.[Phase 2]

    FROM tblEquipPanelBreaker1 AS epb, tblCable1 AS c
    WHERE c.keyShip = 9 AND epb.keyCableDn = c.keyCable;


    Click image for larger version. 

Name:	Type Conversion error.PNG 
Views:	26 
Size:	12.1 KB 
ID:	38571

    Click image for larger version. 

Name:	EquipPanelBreaker1.PNG 
Views:	28 
Size:	23.9 KB 
ID:	38572

    Click image for larger version. 

Name:	tblEquipPanelBreaker.PNG 
Views:	27 
Size:	31.7 KB 
ID:	38573

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    if this is a one time modification why not just add the fields you want added to the existing table.

    If you're worried about maintaining your original data, why not just copy the entire table (data and all) then modify your field names?

  3. #3
    greatwhite is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Mar 2019
    Location
    Ottawa, ON
    Posts
    56
    I'm not sure I follow you?? I need to port the data over from it's existing table the table the data is moving to has the same fields that are required.

  4. #4
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    If you read the error message carefully, you will see that the problem is not Type Conversion Failure, it's Key Violations. Your images don't show the field properties. Maybe you have a field defined with NO DUPLICATES that's causing the problem.

  5. #5
    greatwhite is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Mar 2019
    Location
    Ottawa, ON
    Posts
    56
    Ok So I just check and the Fkeys are set to allow dupes. I also did a test to make sure that the parent table has the corresponding key and it lines up with the data.

  6. #6
    greatwhite is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Mar 2019
    Location
    Ottawa, ON
    Posts
    56
    Any suggestions as to how I might narrow down which field is causing the key violation? I will assume it isn't the primary key since that is auto number and it would probably say primary key.

    epb.keyEquip+10000000 and epb.keyCableDn+10000000 are fkeys. But I seem to be able to find them in the Parent tables

  7. #7
    Minty is offline VIP
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    I think the problem is this statement;
    Code:
    IIF(epb.optA103 = 1, 'Y', 'N')


    The field is a yes/no or Boolean and you are trying to insert text. Try this instead;

    Code:
    IIF(epb.optA103 = 1, Yes, No)




    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  8. #8
    greatwhite is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Mar 2019
    Location
    Ottawa, ON
    Posts
    56
    I adjusted that and still same issue. Since it said key violation and not Pkey I think i can assume it is one or the other Fkeys

    Quote Originally Posted by Minty View Post
    I think the problem is this statement;
    Code:
    IIF(epb.optA103 = 1, 'Y', 'N')


    The field is a yes/no or Boolean and you are trying to insert text. Try this instead;

    Code:
    IIF(epb.optA103 = 1, Yes, No)





  9. #9
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    I would try to remove all calculated values/fields from your query, then start adding them back in until you encounter the error. It's cumbersome but at least you'll know which field is the problem. There's really no way for us to tell you where the problem lies without an example of the database to look at or a better idea of which field/calculated value specifically is causing the problem

    Also, if you are checking the value of a yes/no field -1 is TRUE, 0 is FALSE so your statement should be:

    IIF(epb.optA103 = -1, 'Y', 'N')

  10. #10
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    To narrow it down, in the insert, eliminate a field, then another, then another, etc until the problem goes away.

  11. #11
    Minty is offline VIP
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Quote Originally Posted by rpeare View Post
    Also, if you are checking the value of a yes/no field -1 is TRUE, 0 is FALSE so your statement should be:

    IIF(epb.optA103 = -1, 'Y', 'N')
    Good spot but it's inserting the result value into a boolean so it should be

    IIF(epb.optA103 = -1, True, False) or
    IIF(epb.optA103 = -1, -1, 0) or
    IIF(epb.optA103 = -1, Yes, No)


    But I agree with the others , think you are trying to insert a value somewhere that is upsetting the Access gnomes.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  12. #12
    greatwhite is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Mar 2019
    Location
    Ottawa, ON
    Posts
    56
    OK so I have narrowed it down to the field keyEquip

    by only trying to insert the required fields (Note the Pkey is an autonumber).
    and I get the same error. So definitely something to do with this field but what. The record exists in tblEqupPanel as it should.


    ****Required Fields Only

    INSERT INTO tblEquipPanelBreaker ( keyEquip, numBreakerPosition, numBreakerDetailRow, strBreakerSize )


    Values(10090025,2,1,25)


    Click image for larger version. 

Name:	Capture.PNG 
Views:	19 
Size:	29.9 KB 
ID:	38595

  13. #13
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Try going to design viw for both tables and look at Indices on ribbon. Delete anything that looks out of the ordinary. If you've changed key values and properties in the past, stuff can get left over in the Indices that isn't necessary.

  14. #14
    Join Date
    Apr 2017
    Posts
    1,673
    As start, the whole query doesn't make any sense!

    In tblEquipPanelBreaker the field keyEquip is foreign key, which links the table record with record in table tblEquipPanel. It is OK when tblEquipPanel.keyEquip is used as unique index in tblEquipPanel. When it is foreign key there too, then it is 1st where you get problems, as for every instance of same keyEquip you'll get a separate record in query with exactly same info!

    Then you read keyEquip from tblEquipPanelBreaker, add 10000000 to it, and insert into tblEquipPanelBreaker again. Are you sure the new keyEquip exists in tblEquipPanel, or in table where keyEquip is primary key? When not, then you get a lot of orphan records (records not connected with other tables) in tblEquipPanelBreaker. And are you sure you want those new records you insert, and for which the matching keyEquip exists, to be attached exactly with those records in parent tables?

    And when you insert keyEquip values beyond keyEquip value range in tblEquipPanel into tblEquipPanelBreaker, and keyEquip is an autonumber in some another table, then the seed for ths table remains unchanged, and at some time some value for keyEquip you inseted with query will be inserted - and the record you inserted with query will be then linked to this future entry!

  15. #15
    greatwhite is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Mar 2019
    Location
    Ottawa, ON
    Posts
    56
    OK so after many hours of painstakingly and help on here I've determine this data is screwed. Which is what I told my boss. There are just so many records that don't seem to match up. Howevere because this set of data is quite close to the original data I copied the data. .

    I wish I could give a point for each person who helped

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

Similar Threads

  1. Replies: 7
    Last Post: 11-01-2018, 06:55 AM
  2. Data Type Conversion Error - WHY?
    By Datamulcher in forum Modules
    Replies: 2
    Last Post: 03-13-2017, 06:00 PM
  3. Replies: 4
    Last Post: 11-11-2013, 12:39 AM
  4. Type conversion error
    By corymw in forum Access
    Replies: 1
    Last Post: 07-25-2012, 11:55 AM
  5. Data Type Conversion Error
    By graviz in forum Forms
    Replies: 7
    Last Post: 06-04-2012, 11:34 AM

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