Results 1 to 8 of 8

Thread: Filtering Time

  1. #1
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post
    Hi,

    I have a field in a query called [senttoSAM] that is a date/time field and is set to general date, so that it shows the date and time. I need to do a filter that will filter out any times before 8 am and after 5 pm. I tried

    Between #8:00:00 AM# And #5:00:00 PM#

    But it filters out too many records. I tried doing a separate field that formatted [sendtoSAM] as a medium time but it still filtered out too many records.

    What am I missing?

    Thanks,
    Leesha

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Your condition will only work for records whose senttoSAM value falls on "day zero", i.e. December 30, 1899.

    Add a calculated column to the query:

    TheTime: [senttoSAM]-Int([senttoSAM])

    Clear the Show check box for this column.
    Move the condition

    Between #8:00:00 AM# And #5:00:00 PM#

    from the senttoSAM column to the new column.

  3. #3
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post
    Hi Hans,

    I got an OBDC error stating something about using the convert function to run this query. This database has a few links to sql tables and don't you know this is one of them. I can always create a temporary table using and Access table if there isn't an easy way to change this code, which of course I would need your help on.

    Also.................my never ending need to understand how something works..........................what is the forumula you gave me actually doing? I've not used "Int" before.

    Thanks!
    Leesha
    Attached Images Attached Images

  4. #4
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Internally, dates are stored as whole numbers past Dec 31, 1899 (Zero day) and the time is stored as a fraction of the day - past midnight.

    For example today at noon is 40170.5

    HansV's formula subtracts the whole day portion of the number from the DateTime leaving the time portion.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    I'll have to search for a solution that works for SQL Server tables - SQL Server stores dates/times differently than Access.

    Access stores dates/times as the number of days that have passed since December 30, 1899, 12:00 AM. So for example December 31, 1899 is stored as 1, and January 1, 1900 is stored as 2.
    Times are stored as fractions of a day, so for example January 1, 1900, 6:00 AM is stored as 2.25 (6 hours is 6/24 = 0.25 of a day).
    If you merely specify a time component without a date, it is stored as a fraction between 0 and 1, for example 6:00 PM = 0.75. Times are in fact treated as times on December 30, 1899 since that date is the zero point.
    Now today (December 23, 2009) is 40170, and noon today is 40170.5. To get the time component of 40170.5, we can subtract the integer part of this number. The Int function extracts the integer part of a number: Int(40170.5) = 40170.
    So 40170.5 - Int(40170.5) = 40170.5 - 40170 = 0.5, and this corresponds to 12:00 PM.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Try changing

    TheTime: [senttoSAM]-Int([senttoSAM])

    to

    TheTime: CDate(Format([senttoSam], "hh:nn:ss AM/PM"))

    If you still get errors, try

    TheTime: CDate(Format(Nz([senttoSam], 0), "hh:nn:ss AM/PM"))

  7. #7
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post
    Hi Hans!

    The first one worked perfectly. Thanks to both of you for the explanations. My gosh is time complicated!!!

    Thanks and Merry Christmas,
    Leesha

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Merry Christmas to you too!

    [attachment=87379:xmas.png]
    Attached Images Attached Images

Posting Permissions

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