Goofy Query behavior

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

pvdalen - 29 Sep 2005 21:16 GMT

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

Reply to this Message

KARL DEWEY - 29 Sep 2005 21:29 GMT

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"));

> Hi all,
>
[quoted text clipped - 18 lines]
> Thanks,
> Paul

Reply to this Message

Michel Walsh - 29 Sep 2005 21:40 GMT

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

> Hi all,
>
[quoted text clipped - 20 lines]
> Thanks,
> Paul

Reply to this Message