Rick,
>It wasn't until I set the WHERE clause up as
>>WHERE ((Activities.Schedl_For = 26) AND (Activities.Start_Time > 6/17/2004))
> OR ((Activities.Schedl_For = 26) AND (Activities.Start_Time >= 5/25/2004 AND Activities.Start_Time < 5/26/2004))
>
>That I got
>the 1 record for the 5/25/2004
>and the 1 record for the future
>
>Seemed strange to me.... oh wellWow, that seems weird to me too. You shouldn't have had to combine the Schedl_For with the dates like that. I wonder if you had your parentheses wrong somewhere and didn't realize it? (although they look fine in your example).
~~Bonnie
>One more item along this line of thought... (ignoring TIME stuff)
>
>My WHERE clause that I was building,
>>> WHERE (Activities.Schedl_For = 26)
>>> AND (Activities.Start_Time = 6/17/2004)
>is intended to return work done in a date range for a specific user_ID (stored in Activities.Schedl_For)
>The date range could be:
>All
>Today
>Past
>Future
>or between a Start and End date
>
>When the WHERE clause was
>
>WHERE (Activities.Schedl_For = 26)
> AND (Activities.Start_Time > 6/17/2004)
>
>It returned the 1 record for the future
>
>
>When the WHERE clause was
>
>WHERE (Activities.Schedl_For = 26)
> AND (Activities.Start_Time >= 5/25/2004 AND Activities.Start_Time < 5/26/2004)
>
>It returned the 1 record for the 5/25/2004
>
>
>When the WHERE clause was
>
>WHERE (Activities.Schedl_For = 26)
> AND ((Activities.Start_Time > 6/17/2004)
> OR (Activities.Start_Time >= 5/25/2004 AND Activities.Start_Time < 5/26/2004))
>
>It returned the 1 record for the future
>
>It wasn't until I set the WHERE clause up as
>
>WHERE ((Activities.Schedl_For = 26) AND (Activities.Start_Time > 6/17/2004))
> OR ((Activities.Schedl_For = 26) AND (Activities.Start_Time >= 5/25/2004 AND Activities.Start_Time < 5/26/2004))
>
>That I got
>the 1 record for the 5/25/2004
>and the 1 record for the future
>
>Seemed strange to me.... oh well
>
>
>Rick
>
>>Rick,
>>
>>I've never used Access, but in SQL Server you need to worry about the time as well as the date. Since you're not using a time, it's assumed to be 00:00:00. I'm not sure about the Today() method in your code, maybe it's different in VB, but in C# one uses a property DateTime.Today. At any rate, the time is still 00:00:00. So, you really should never do an equal when you're talking about dates. Change it to:
>>
>> myProposedFilter = myProposedFilter & " AND " & _
>> " (Activities.Start_Time >= " & Today() & " OR " &_
>> " Activities.Start_Time < " & Today().AddDays(1) & ") "
>>
>>Not 100% sure if that syntax will work, but you get the picture. Play with it.
>>
>>HTH,
>>~~Bonnie
>>
>>
>>>I have a simple question...
>>>My question is,
"What is the correct way to do this date comparison?">>>The way I'm doing it now doesn't work.
>>>
>>>I have my data in an Access table
>>>I have a field (Start_Time), declared as a
Date/Time>>>I want to return all records where the Start_Time is from today.
>>>
>>>The select statement I'm using is
>>>
>>>SELECT Activities.Unique_ID as Activity,
>>> Activities.Contact_ID as Contact,
>>> Contacts.Company as Company,
>>> Activities.Type,
>>> Activities.Start_Time as StartDate,
>>> Activities.Duration,
>>> Activities.Priority,
>>> Activities.Regarding,
>>> Users.User_Name as For_User,
>>> Activities.recurring_freq as Frequency,
>>> Activities.recurring_until as Recurring_Until,
>>> Activities.Completed as Completed
>>> FROM (Activities
>>> INNER JOIN Users
>>> ON Activities.schedl_for = Users.Unique_ID)
>>> INNER JOIN Contacts
>>> ON Activities.Contact_ID = Contacts.Unique_ID
>>> WHERE (Activities.Schedl_For = 26)
>>> AND (Activities.Start_Time = 6/17/2004)
>>> ORDER BY Activities.Start_Time DESC
>>>
>>>
>>>The WHERE clause is actually being build in code...
>>>
>>>...
>>> myProposedFilter = myProposedFilter & " AND " & _
>>> " (Activities.Start_Time = " & Today() & ") "
>>>...
>>>Session("myProposedFilter") = myProposedFilter
>>>...
>>>SelectCommand = "SELECT " & _
>>> "Activities.Unique_ID as Activity, " & _
>>> "Activities.Contact_ID as Contact, " & _
>>> "Contacts.Company as Company, " & _
>>> "Activities.Type, " & _
>>> "Activities.Start_Time as StartDate, " & _
>>> "Activities.Duration, " & _
>>> "Activities.Priority, " & _
>>> "Activities.Regarding, " & _
>>> "Users.User_Name as For_User, " & _
>>> "Activities.recurring_freq as Frequency, " & _
>>> "Activities.recurring_until as Recurring_Until, " & _
>>> "Activities.Completed as Completed " & _
>>> "FROM (Activities INNER JOIN Users ON Activities.schedl_for = Users.Unique_ID) " & _
>>> " INNER JOIN Contacts ON Activities.Contact_ID = Contacts.Unique_ID " & _
>>> "WHERE " & Session("myProposedFilter") & _
>>> " ORDER BY Activities.Start_Time DESC "
>>>>>>
>>>TIA,
>>>Rick
