Results 1 to 3 of 3
  1. #1
    Rumbl3r is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2013
    Posts
    2

    Access crashes when importing text file to table with calculated field

    I'm creating a table where I import data from a csv file every day, one of the fields being a text version of a time duration for a task like "123:45" or "12:34". Being as I can't use this data form to calculate with or determine an average duration I decided to create a calculated field to give me decimal hours like 123.5 or 12.5



    This screenshot shows how the calculated field is created (small pic?) but the calculation for Actual Hours is Left([Actual Duration],InStr(1,[Actual Duration],":",2)-1)+(1/(60/(Mid([Actual Duration],InStr(1,[Actual Duration],":",2)+1,2)+1)))

    Click image for larger version. 

Name:	Calc field.JPG 
Views:	13 
Size:	37.4 KB 
ID:	10955


    This was working fine on the day I did it, but since the database has been closed, it no longer imports the data and crashes Access instead! I've tried creating a copy of the table with empty data and it might work sometimes, but never once there's already data in the table. I've also tried a number of csv files from differing days, and they're all the same.

    Without the calculated fields in the table, it will import any files I try, but the calculation works, so don't quite understand and I don't get any error messages.

    Any thoughts?

  2. #2
    alansidman's Avatar
    alansidman is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,529
    Have you tried doing the calculation in Excel in the CSV file before importing. It might be easier.

  3. #3
    Rumbl3r is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2013
    Posts
    2
    Quote Originally Posted by alansidman View Post
    Have you tried doing the calculation in Excel in the CSV file before importing. It might be easier.
    The CSV comes from an external system not Excel, so unfortunately I can only use the raw data as it's presented.

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

Similar Threads

  1. Replies: 5
    Last Post: 02-20-2011, 08:22 PM
  2. Importing a text file to access
    By Navop in forum Access
    Replies: 2
    Last Post: 11-11-2010, 07:15 PM
  3. Replies: 1
    Last Post: 11-05-2010, 04:31 PM
  4. Importing text file into Access Table
    By Anthony in forum Import/Export Data
    Replies: 13
    Last Post: 09-23-2009, 04:47 PM
  5. Importing a tab delimited file into access table - please he
    By championss in forum Import/Export Data
    Replies: 0
    Last Post: 10-29-2006, 02:33 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