Results 1 to 8 of 8
  1. #1
    Bzltyr is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2019
    Posts
    2

    Type conversion failure

    I want to automate a macro but I get this "Type Conversion Failure" message so I have to click okay to get the macro to complete. How do I locate the on record that is at issue. I have manually compared all of the fields I am using to create a table from a larger table and I get this one error. Is there a way to get a hint as to what field the type failure resides? Or, the particular value?



    Thanks.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    We need something to analyze. Provide code and sample data. If you want to provide db for analysis, follow instructions at bottom of my post.

    What do you mean by 'creating a table from a larger table'? Exactly what are you trying to accomplish?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Bzltyr is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2019
    Posts
    2
    Thanks for the reply. I am not using code. I am going to Create/Query Design/Make Table. I am using the table named "Input" and adding certain fields and making a table named "Commisions." I get the error message that says 1 record has a type conversion failure and null value will be entered.

    I cannot find the error manually through searching for a filed that has a different type than the type of the target table.

    In general, is there a way to find the errors. I also get an error when appending. I have thousands of records and in 93 columns/fields. Too much to go searching. Can Access report on the offending field?

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    If the error message doesn't identify field of issue, I don't know any way for Access to report it otherwise.

    Again, don't have anything I can analyze.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    GinaWhipp's Avatar
    GinaWhipp is offline Competent Performer
    Windows 7 64bit Access 2013 32bit
    Join Date
    Jul 2011
    Location
    Ohio, USA
    Posts
    377
    Type Conversion Error usually means you are trying to put TEXT into a NUMERIC field or the wrong date in a Date\Time field, i.e. 6/31/2019 (no such date). No way to tell what record this requires using your eyeballs to find it.

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I would have thought trying to enter invalid values would generate "The value you entered is not valid for...".
    My guess is that the first table contains at least one null in at least one record (there is no value). You can get that error during this operation or when you attempt to use a conversion function on null.
    You would have to enter a value that you can remove after the table is made. If the fields are text I think you could convert all nulls to empty strings, make the table and then convert back to null.
    Last edited by Micron; 08-24-2019 at 08:05 AM. Reason: clarification
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    GinaWhipp's Avatar
    GinaWhipp is offline Competent Performer
    Windows 7 64bit Access 2013 32bit
    Join Date
    Jul 2011
    Location
    Ohio, USA
    Posts
    377
    @Micron

    Not all the time. Those error messages can be VERY deceptive and sometimes useless.

  8. #8
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    to create a table from a larger table
    not sure how you can be getting the error since the field created by the maketable will take on the same characteristics of the source field. So I suspect you are populating the new table with some calculated values - and it will be one of those calculations that is causing the problem. Remove each one, one at a time until the error goes away. Having identified the column, you can now check the rows. First thing you know is the field in the new table will be null - so that should limit the rows to need to evaluated

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

Similar Threads

  1. Type Conversion Failure with Access 2013
    By squeasel in forum Access
    Replies: 9
    Last Post: 07-11-2017, 10:03 PM
  2. Type Conversion Failure
    By jessgold in forum Access
    Replies: 5
    Last Post: 11-12-2012, 10:39 PM
  3. Type Conversion Failure When Importing txt File
    By Eka1618 in forum Import/Export Data
    Replies: 15
    Last Post: 10-29-2012, 06:45 PM
  4. Help need: Type conversion failure
    By tami in forum Access
    Replies: 1
    Last Post: 01-18-2012, 07:18 AM
  5. Type Conversion Failure
    By fpmsi in forum Access
    Replies: 7
    Last Post: 09-22-2011, 11:25 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