[Reply]
HI Jeremy,
First off, what are you really wanting? .... from what I can see the query is
asking is if any of the content between START and END lies within the specified
range ....22/2 - > 26/2.
Viewing it pictorially .. x denotes the START END range
20 21 22 23 24 25 26 27
x x fail
x x x x accept
x x x accept
x x x x accept
x fail
here goes:
select ...
where ( END>=#22 FEB 2010 00.00.00# and END <=#26 FEB 2010 00.00.00# )
or ( START>=#22 FEB 2010 00.00.00# and START<=#26 FEB 2010 00.00.00# )
Note this is untested, but should get you in the right direction.
HTH
Gary
At 09:52 on 31/01/2010 you wrote
>To : delphi@delphi.org.nz
>CC : >From: Jeremy Coulter, jscoul...@gmail.com
>Content Type: text/html
>Attached: >
>This is a multipart message in MIME format.
>
>
>
>
>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
Ref#: 41006
_______________________________________________
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
