Filtering Time

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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”))

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

Merry Christmas to you too!

Reply | Quote