Results 1 to 8 of 8
  1. #1
    4 Star Lounger
    Join Date
    Mar 2005
    Location
    Leicestershire, United Kingdom
    Posts
    506
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Time Parameter in Query

    Hi,

    I have a query where I am trying to find matches depending on the time.

    In the query it looks at though the time is stored as hh:mm:ss.

    If I enter 17:00:00 in the query criteria, no results come back, when I know there are atleast 500!

    Anyone have any pointers?
    Best Regards,

    Luke

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    What happens if you put in between #16:59:59# and #17:00:01#

    Did the times actually get typed in by someone? or where they captured in some other way? I am wondering if the data as stored is not the same as the data as displayed?
    Regards
    John



  3. #3
    4 Star Lounger
    Join Date
    Mar 2005
    Location
    Leicestershire, United Kingdom
    Posts
    506
    Thanks
    0
    Thanked 0 Times in 0 Posts
    The times are entered manually. There are input masks on the fields you do this do though.

    I have tried the formula you suggest and still nothing.
    Best Regards,

    Luke

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Can you post a sample of the data?
    Regards
    John



  5. #5
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    Have you tried using the Datepart function?

  6. #6
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    A Date/Time field is used to store both dates and times within the one datatype. These are numbers with the whole number component representing days, and the decimal component representing the time. A Date/Time field formatted in a time format will hide any whole number component, but that would still be relevant for query criteria.
    So this is one possible explanation for the problem you have. So how can we extract just the Time out of a Date/Time field?

    The DatePart function can extract either the hours, minutes or seconds. e.g. Datepart("h",[datefield]) would extract the hour component. o you could use 3 DatePart functions, then combine the results to produce a time.

    Another option uses the fact that these are just numbers: Int([DateField]) returns just the whole number part of the Datefield. Subtract that from the original date, and just the time will be left.
    Regards
    John



  7. #7
    4 Star Lounger
    Join Date
    Mar 2005
    Location
    Leicestershire, United Kingdom
    Posts
    506
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have found that, my table is a link table to a sQL backend.

    If I import the table and run the query it finds the matches.

    The information you list above just returns 00:00
    Best Regards,

    Luke

  8. #8
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    That's an important piece of information. I presume you mean it is a linked table in SQL Server. Depending on the type of field the table is defined as in SQL Server, the calculation may need to vary. However the suggestion of using the DatePart() function should give you the time values regardless of the data type in SQL Server.
    Wendell

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •