ACCESS FEED: [MS_AccessPros] Query returns a record that has an enddate even though EndDate=null in criteria

Selasa, 18 Desember 2012

[MS_AccessPros] Query returns a record that has an enddate even though EndDate=null in criteria

Good afternoon/evening !

I've been working away and thought I was almost done but have encountered two problems I can't work through. This message is about one of those.

I have a query that is returning one record that has an EndDate even though EndDate is null in criteria. It's been a while since I sent you all of the SQL's so I'm hoping I'm sending you all the info you need.

The summary of what is happening, is that I'm wanting all buyers with unended LookingEvents (No End Date entered). Spouses that have the same last name and are in qCustomerList_2ContactNameSpID are combined in one line. The spouse that has the lowest Ordr number is the main contact. There are other misc adjustments.

When I look at the query results, the EndDate for one record is empty even though it is entered in the LookingEvent table. When I do a simple query with only the LookingEvent table WHERE EndDate IsNull, it does not pull up that record.

Eek! What's happening? This is the only place where this is happening.

Thanks a zillion! The code is below
Connie

SELECT LookingContacts.ContactID, LookingEvent.BuyerTyp AS StatusID, DatePart("yyyy",[BeginDate]) AS Yr, LookingEvent.LookingID AS ID, LookingEvent.Agent AS AgentID, qCustomerList_2ContactNameSpID.SpID, "Look" AS Tbl, Null AS HomeInfoID, LookingEvent.Notes AS Notes2, Null AS PropertyName, Contacts.LastName, Contacts.FirstName, Contacts.SpouseFN, LookingEvent.EndDate, LookingContacts_1.LookingID, LookingContacts.Ordr
FROM LookingEvent LEFT JOIN (Contacts RIGHT JOIN ((LookingContacts LEFT JOIN qCustomerList_2ContactNameSpID ON LookingContacts.ContactID = qCustomerList_2ContactNameSpID.ContactID1) LEFT JOIN LookingContacts AS LookingContacts_1 ON qCustomerList_2ContactNameSpID.SpID = LookingContacts_1.ContactID) ON Contacts.ContactID = LookingContacts.ContactID) ON LookingEvent.LookingID = LookingContacts.LookingID
WHERE (((qCustomerList_2ContactNameSpID.SpID) Is Not Null) AND ((Contacts.LastName)=[SpouseLN]) AND ((LookingEvent.EndDate) Is Null) AND ((LookingContacts_1.LookingID) Is Null)) OR (((qCustomerList_2ContactNameSpID.SpID) Is Not Null) AND ((Contacts.LastName)=[SpouseLN]) AND ((LookingEvent.EndDate) Is Null) AND ((LookingContacts_1.LookingID) Is Not Null) AND ((LookingContacts.Ordr)<[LookingContacts_1].[Ordr])) OR (((qCustomerList_2ContactNameSpID.SpID) Is Not Null) AND ((Contacts.LastName)<>[SpouseLN]) AND ((LookingEvent.EndDate) Is Null)) OR (((qCustomerList_2ContactNameSpID.SpID) Is Null) AND ((LookingEvent.EndDate) Is Null));