Results 1 to 7 of 7
  1. #1
    CharlotteAllen is offline Novice
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    3

    Access into Excel - Format Issues

    Hi,



    I have a query in access which contains some columns in a duration format hh:mm:ss, in access the format is correct and shows correctly. However when imported to Excel they all revert to 00:00:00 and I can't understand why as all the other formats text, number and % all work.

    If there anything specific you need to do for time, either in the Excel sheet or prior in Access? Thank you in advance

  2. #2
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Is this field in Access of date/time data type, or text?
    How, exactly are you importing the data into Excel?

  3. #3
    CharlotteAllen is offline Novice
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    3
    Hi,
    Yes it is in Date/Time format, I've tried text format too and this works but then doesn't allow formulas when in excel.
    It's imported into Excel through - Data > From Other Sources > From Microsoft Query

  4. #4
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Why are you using "From Microsoft Query"?
    That is not the same as Access.
    Try Get Data --> From Database --> From Microsoft Access Database, and see if that works better.

  5. #5
    CharlotteAllen is offline Novice
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    3
    Wow thank you so simple!!

  6. #6
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    You are welcome.
    Yep, Microsoft Query is something different, so it may not translate well into Access, as cause the type of behavior you were experiencing.

  7. #7
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by CharlotteAllen View Post
    some columns in a duration format hh:mm:ss,
    Please note that hh:mm:ss is a time format, NOT a duration format.

    You can say "I got up at 8:30 this morning".

    But you cannot/should not/would not say "I had to drive 8:30 to get to the meeting".
    You WOULD say "I had to drive 8 hours and 30 minutes to get to the meeting" or "I had to drive 8.5 hours to get to the meeting".

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

Similar Threads

  1. format of Excel to Access
    By Erictsang in forum Access
    Replies: 1
    Last Post: 10-04-2017, 04:57 AM
  2. Import from Excel to Access 2010 Issues
    By bcofie in forum Import/Export Data
    Replies: 7
    Last Post: 01-20-2014, 10:12 AM
  3. Replies: 3
    Last Post: 09-08-2013, 08:50 AM
  4. Replies: 4
    Last Post: 12-17-2012, 01:21 PM
  5. Replies: 3
    Last Post: 02-01-2011, 10:18 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