Goofy Query behavior

[Guest]

Hi all,

This is a little strange. I have the following query:

SELECT Worker.WorkerName, TaskAll.*
FROM Worker RIGHT JOIN TaskAll ON Worker.WorkerID = TaskAll.WorkerID
WHERE (((TaskAll.DateOrdered)<=#9/3/2005#) AND
((TaskAll.DateEntered)>=#9/22/2005#) AND ((TaskAll.Market)="jackson"));

which works just fine. If I change only this part:

=#9/22/2005#

to
<=#9/22/2005#
, which asks for dateEntered less than AND equal to the 22nd, I don't get
the records that match the 22nd.

Can anyone please explain to me why the records for the 22nd are captured in
the "greater than" query but not the "less than" query? Do I have to specify
a time as well as a date?

Thanks,
Paul

 

[Guest]

Your date could have been enter with a time component. Try this --

SELECT Worker.WorkerName, TaskAll.*
FROM Worker RIGHT JOIN TaskAll ON Worker.WorkerID = TaskAll.WorkerID
WHERE (((Format([TaskAll].[DateOrdered],"m/d/yyyy"))<=#9/3/2005#) AND
((Format([TaskAll].[DateEntered],"m/d/yyyy"))>=#9/22/2005#) AND
((TaskAll.Market)="jackson"));

 

[Michel Walsh]

Hi,



because 5/22/2005 00:00:01 is later than 5/22/2005, at midnight
( 00:00:00 )


Your date_time field contains, probably, in addition to the date itself.


A possible trick is to add ONE day, and strict inequality:


< #9/23/2005#



since, by default, when there is no time, it is at midnight. That will so
include all the possible time having the #9/22/2005# as date.



Hoping it may help,
Vanderghast, Access MVP