Results 1 to 5 of 5
  1. #1
    gar is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2011
    Posts
    42

    Type conversion error

    I tried to formulate an import specification to import telephone account data from a .csv file. Each line in the input file included the phone number as a ten-digit string. The specification loaded this into a short text field in the table. In practice, numbers greater than 2147483647 (i.e. 2^31 - 1) elicited a "Type conversion" error. Clearly, despite the fact that Short Text was specified, the system was interpreting this as a numerical value. Does anyone know why this should be happening and how it can be avoided?

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    i get this a lot. I open the file in excel run this routine to convert the column to text, then save it to csv.
    Then it loads just fine.

    Code:
    Public Sub Cvt2Txt()
    Dim vFile, vLine
    
    
    While ActiveCell.Value <> ""
         If Left(ActiveCell.Value, 1) <> "'" Then ActiveCell.Value = "'" & ActiveCell.Value
         
         ActiveCell.Offset(1, 0).Select  'next row
    Wend
    End Sub

  3. #3
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    When you import a CSV or Excel file, unless you specify the datatypes, Access will try to 'intelligently' determine the datatype based on the First few records for each field.
    So this can cause phone numbers to be interpreted as numbers when they should be text.
    To prevent this problem, do one of the following
    1. Specify the datatype of any field liable to misinterpretation as part of your import specification
    2. Link the CSV/Excel file rather than import it then run a query or vba procedure to append the data to a predefined table with the correct datatype for each field.

    Having dealt with this issue for many years, I always use method 2 as it gives me total control over the data
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  4. #4
    gar is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2011
    Posts
    42
    Thanks very much. I don't know much about Excel and only started to look at it's data types in response to your message. Why is it not sufficient just to format the cells as text (rather than "general" which is what they were). When I tried this the character string (i.e. the phone number) changed from left-justified to right, which I took as a good sign, but when I closed Excel and reopened it the format had reverted to general and the justification to right.

  5. #5
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Quote Originally Posted by gar View Post
    Thanks very much. I don't know much about Excel and only started to look at it's data types in response to your message. Why is it not sufficient just to format the cells as text (rather than "general" which is what they were). When I tried this the character string (i.e. the phone number) changed from left-justified to right, which I took as a good sign, but when I closed Excel and reopened it the format had reverted to general and the justification to right.
    As already explained, you can't rely on Access interpreting the CSV/Excel datatypes correctly. That was why I recommended the two approaches outlined in my previous reply. If you use method 2, the results will always be correct.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

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

Similar Threads

  1. Type Conversion Error
    By greatwhite in forum Queries
    Replies: 14
    Last Post: 06-06-2019, 06:14 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