Finding Date Conflicts 1

[drichter12]

I am trying to find date conflicts in a query but not having much luck.

Field1 Field2 Field3
07/15/2011 05/10/2011 06/01/2011
03/20/2011 03/19/2011 03/29/2011
04/22/2011 04/22/2011 04/30/2011
06/05/2011 07/03/2011 #NULL


Field1 would be the date to compare, 2 and 3 would be either a range of dates or just a single date in Field2. I want to find all records where Field1 conflicts with either Field2 or Field3 or any dates between the two if it is a range.

Thanks,


Dale

 

[PWise]

select Field1,field2 , field3
from tablename
where field1 between [field2] and nz([field3],[field3])

 

[Andrzejek]

... or rather
[tt]select Field1,field2 , field3
from tablename
where [red]NOT ([/red]field1 between [field2] and nz([field3],[field3]) [red])[/red][/tt]

Have fun.

---- Andy

 

[drichter12]

Thanks so much, it works perfect. I will get the hand of this eventually. What is "NZ" for?


Dale

 

[drichter12]

Andy,

PWise had it correct, I wanted to find only records that conflict.

Thank you for the quick response though.


Dale

 

[traingamer]

I think you really want this:

select Field1,field2 , field3
from tablename
where field1 between [field2] and nz([field3],[red][field2][/red])

Greg
People demand freedom of speech as a compensation for the freedom of thought which they seldom use. Kierkegaard