[DUG] another set of eyes

[Jeremy Coulter]

Hi All.

 

I am the stage with  problem that SOMEONE who's a bit more lateral thinking
than me might be able to see the solution.

 

I have the following data (just a snapshot).

 

Item   Start                        End

1          15 Feb 2010          26 Feb 2010

2          22 Feb 2010          26 Feb 2010

3          15 Feb 2010          29 Feb 2010

4          24 Feb 2010          25 Feb 2010

5          23 Feb 2010          25 Feb 2010

6          24 Feb 2010          31 Feb 2010

7          22 Feb 2010          26 Feb 2010

8          23 Feb 2010          24 Feb 2010

 

What I am doing is TRYING to find all items that fall between a given date
range.

In this case I am trying to find all the items between the 22 Feb and 26
Feb.

i.e. all of the items should be returned in the above snap shot.

 

I am trying the following where clause (not real field names):-

 

WHERE (#22 FEB 2010 13.00.00# Between START And END)

AND (#26 FEB 2010 10.00.00# Between START And END)

 

However, that missed out Items 1,34,5,6 and I think 8.

If I try:

 

WHERE START >=#22 FEB 2010 13.00.00# And ENDTIME <=#26 FEB 2010 10.00.00# 

It misses out 1,3 and 6

 

I cant go

WHERE START >=#22 FEB 2010 13.00.00# or ENDTIME <=#26 FEB 2010 10.00.00# as
that returns to many records.

 

Can anyone see what I am missing? I don't mind if I have to do it in
separate queries as I can just union them, but I have been looking at this
too long and I just cant figure it out, so I thought another set of eyes
might help J

 

Thanks, Jeremy

  

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://listserver.123.net.nz/pipermail/delphi/attachments/20100131/7e8b8e4b/attachment.html