Jeremy I might be missing something but an "in range" query woult be
ItemStart >= RangeStart AND ItemEnd <= RangeEnd just as a range overlap would be ItemStart <= RangeEnd AND ItemEnd >= RangeStart HTH Neven > > 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 > > ------------------------------------------------------------------------ > > _______________________________________________ > NZ Borland Developers Group - Delphi mailing list > Post: delphi@delphi.org.nz > Admin: http://delphi.org.nz/mailman/listinfo/delphi > Unsubscribe: send an email to delphi-requ...@delphi.org.nz with Subject: > unsubscribe _______________________________________________ NZ Borland Developers Group - Delphi mailing list Post: delphi@delphi.org.nz Admin: http://delphi.org.nz/mailman/listinfo/delphi Unsubscribe: send an email to delphi-requ...@delphi.org.nz with Subject: unsubscribe
