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
