Showing overlapping Dates

[Jeff]

I am using th following code to query records where the customer's service
start and end dates fall within a specified date range. The problem with
this code is when I have a non determined ServiceEnd (not entered).
Active customers may not have the "ServideEnd". Any Ideas on how to modify
or rewrite this code so that it also shows the records with a null entry on
the ServiceEnd???

(([RangeStart])<=[ServiceEnd]) AND (([RangeEnd])>=[ServiceStart]))

 

[RicOl]

I would have used the following code idea. When ServiceEnd is Null put in a value long away in tte future say: 12/31 2199.
Dim ServiceEndLongAway
If IsNull(ServiceEnd) Then
ServiceEndLongAway = #12/31 2199#
Else
ServiceEndLongAway = ServiceEnd
Endif
(([RangeStart])<=[ServiceEndLongAway ]) AND (([RangeEnd])>=[ServiceStart]))
If you use a Form field this new field could be invisible...


"Jeff" skrev:

 

[Douglas J. Steele]

(([RangeStart])<=Nz([ServiceEnd], #9999/12/31#)) AND
(([RangeEnd])>=[ServiceStart]))

 

[Dale Fye]

Jeff,

Assuming that [ServiceEnd] will be NULL if it is not entered, rather than
some default value, try:

(([RangeStart])<=(NZ([ServiceEnd], Date()) AND
(([RangeEnd])>=[ServiceStart]))

But the more I look at it, that might not be what you are looking for. You
might want to change the reference to Date() to something like DateAdd("y",
1, [ServiceStart]) if you just want to assume that the service contract
would be for 1 year.

HTH
Dale