I shall give that a try and get back to you. You know how it is, look at something long enough and you eventually loose site of what you are trying to do ....aghhh......
Jeremy From: delphi-boun...@delphi.org.nz [mailto:delphi-boun...@delphi.org.nz] On Behalf Of Xander (GMail) Sent: Sunday, 31 January 2010 10:47 To: 'NZ Borland Developers Group - Delphi List' Subject: Re: [DUG] another set of eyes - correction Sorry, correction (on last line): If I understand your problem correctly, the following should work: WHERE -- those that begin inside the range period: (START >= RangeStart AND START <= RangeEnd) OR -- those that end inside the range period: (END >= RangeStart AND END <= RangeEnd) OR -- those that begin before and end after the range period: (START < RangeStart AND END > RangeEnd) _____ From: Xander (GMail) [mailto:xander...@gmail.com] Sent: Sunday, January 31, 2010 10:45 AM To: 'NZ Borland Developers Group - Delphi List' Subject: RE: [DUG] another set of eyes Hi Jeremy, If I understand your problem correctly, the following should work: WHERE -- those that begin inside the range period: (START >= RangeStart AND START <= RangeEnd) OR -- those that end inside the range period: (END >= RangeStart AND END <= RangeEnd) OR -- those that begin before and end after the range period: (START <= RangeStart AND END < RangeEnd) Cheers _____ From: delphi-boun...@delphi.org.nz [mailto:delphi-boun...@delphi.org.nz] On Behalf Of Jeremy Coulter Sent: Sunday, January 31, 2010 9:54 AM To: 'NZ Borland Developers Group - Delphi List' Subject: [DUG] another set of eyes 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
