Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Shaun E on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

boolean operators and dates problem

Status
Not open for further replies.
Sep 25, 2002
159
US
Hello everyone, I discovered a tricky problem in my query. I checked the data against my results and something weird is happening. I copied my WHERE statement below because this is where the problem is happening. The part where I have tableName.[Close Date] <= tableName.[Original Commit Date] is the problem. In my database I have an entry where the Close Date and Original Date are equal to each other. This query below is not picking it up. But when I changed the operator to be greater than (tableName.[Close Date] > tableName.[Original Commit Date]), it displayed the entry??? Should I not be comparing dates like this??


WHERE BarcelonaInternalActions.Status="Closed" And BarcelonaInternalActions.[Close Date]<=BarcelonaInternalOGCommitDate.[Original Commit Date] And (BarcelonaInternalActions.[Close Date]>=DateValue("2/27/2006") And BarcelonaInternalActions.[Close Date]<DateValue("3/6/2006"))
 
If the DateTime fields hold time info:
... And DateValue(BarcelonaInternalActions.[Close Date]) <= DateValue(BarcelonaInternalOGCommitDate.[Original Commit Date]) And ...

Another way:
... And Int(BarcelonaInternalActions.[Close Date]) <= Int(BarcelonaInternalOGCommitDate.[Original Commit Date]) And ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886
 
Check your table and examine the values of the relevant fields but display them in a full

mm/dd/yyyy hh:nn:ss

format. DateTime fields that appear to be equal if you just look at the date part of the field may in fact not be equal when the full Date and Time values are considered.

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top