Results 1 to 2 of 2
  1. #1
    aroed is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Dec 2011
    Posts
    1

    Filter time shows wrong result, bug in Access or Excel?

    Have a table in Excel 2010 with a column formated as hour:minutes:seconds (00:00:00). First row is 00:00:00, second 00:00:01, and so it continues to 00:05:00.

    Have a table in Access 2010 with a field formated as date/time, hh:nn:ss.



    Copy column from Excel and paste into Access.

    When doing some filtering in the table (right click in the first cell "is like 00:00:00", the query shows the result ok.

    But when I try som other, i.e. 00:00:04, access won't return any result.

    I have tried on several pc, and several versions. But I think Access or Excel must have a bug.

    I can override the problem by choosing paste as special, and choose paste as text instead of paste as excel.

    Someone help me?

  2. #2
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    I do not know much about Excel, but Access stores dates/times as a number. The format you see is due to the date/time settings on your computer.


    For example, 4 seconds after midnight on 12/20/2011 would be stored as this numerical value 40897.0000462963

    The part of the number to the left of the decimal point is the number of days since 12/31/1899 (if I remember the date correctly). The part of the number to the right of the decimal represents the fraction of a day. (so .5 would be 1/2 a day or noon).


    So Access isn't really storing 00:00:04 as 4 seconds.

    I would recommend converting all your times into seconds and store that number, you can always convert the total time in seconds to look like 00:00:00

    Alternatively, you could store the 00:00:00 value as text but you would not be able to do any arithmetic operations to the value.

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

Similar Threads

  1. Replies: 4
    Last Post: 02-13-2013, 02:35 PM
  2. Replies: 7
    Last Post: 08-04-2011, 07:49 PM
  3. What's wrong with this filter?
    By jgelpi16 in forum Queries
    Replies: 4
    Last Post: 02-21-2011, 12:58 PM
  4. Simulating Excel Filter in Access!
    By Alinagoo in forum Queries
    Replies: 0
    Last Post: 02-28-2010, 01:05 AM
  5. Using Access for an Excel like filter?
    By crownedzero in forum Queries
    Replies: 0
    Last Post: 06-15-2009, 01:55 PM

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