What's wrong with this SQL statement?

[ksutton]

I don't get all of the desired results when I use the following SQL statement. Can anyone help????

I need the info for all units that have:
1. R17 = 1

AND

2. Started within the selected range (10/20-10/27).
OR
3. Stopped within the selected range.
OR
4. Started previous to the range and has not yet stopped.

SELECT Data_Unavailable.*, SiteDefinition.CustomerName
FROM Data_Unavailable, EquipmentDefinition, OSMDefinition, 
    SiteDefinition
WHERE (Data_Unavailable.SerialNumber = EquipmentDefinition.SerialNumber
     AND 
    EquipmentDefinition.OSMName = OSMDefinition.OSMName AND
     OSMDefinition.SiteName = SiteDefinition.SiteName AND 
    Data_Unavailable.R17 = 1) AND 
    ((StartDateTime >= '10/20/2000' AND 
    StartDateTime < '10/28/2000') OR
    (StopDateTime >= '10/20/2000' AND 
    StopDateTime < '10/28/2000') OR
    (StopDateTime = NULL))
ORDER BY Data_Unavailable.SerialNumber

I have several units that started before the selected range and have a StopDateTime of NULL, but they are not returned with the Recordset.

Thanks for the help!!!!

((StartDateTime >= #10/20/2000# AND 
    StartDateTime < #10/28/2000#) OR
    (StopDateTime >= #10/20/2000# AND 
    StopDateTime < #10/28/2000#) OR
    (StopDateTime = NULL))

that should just about do it...

[ksutton]

This is a M$ SQL Server 7.0 machine. The #'s do not work. I have to wrap the dates with quotes. Or else I get a "inavlid command near #" error.

Thanks!

what about Chr(35)

[theroper]

O.k. would need to check this but here goes!

SELECT Data_Unavailable.*, SiteDefinition.CustomerName
from EquipmentDefinition
inner join Data_Unavailable on
Data_Unavailable.SerialNumber = EquipmentDefinition.SerialNumber
inner join OSMDefinition.SiteName on
EquipmentDefinition.OSMName = OSMDefinition.OSMName
where
--Cant see the point of this join??!!
(OSMDefinition.SiteName = SiteDefinition.SiteName AND
Data_Unavailable.R17 = 1) AND
((StartDateTime between '10/20/2000' AND
'10/28/2000') OR
(StopDateTime between '10/20/2000' AND
'10/28/2000') OR
(StopDateTime = NULL))

Try that..Should be better