Return records with several null date fields in one record, in one table.

[JTKrol]

Hey,
I was not at all clear in what I am trying to do when I posted this hope this edit helps...

I have three date fields that that are associated with all people in the db I want to query. I only want records that have a null in any of the three fields to be returned, and if 1 or 2 of the other fields have dates in them those dates should show as well (see below).

Problem is one person can have a null field in one, two or all three records. I tried using the Is Null but when I line them up my query only returns records that have all three dates null...

I've tried Ruegen's solution but that does not return what I need either...



With that give me a result similar to this? John Smith has the FP and PDCA in but is missing the StPDChk, Jane smith has the FP and StPDChk but is missing the PDCA and Mike Smith has nothing turned in.

Last Name First name DOB Supervisor FPMissing StPDChkMissing PDCAMissing
Smith John 01/01/1980 Mike Jones 12/10/2015 11/09/2015
Smith Jane 11/01/1992 Sue Mi 12/10/2015 11/09/2015
Smith Mike 02/01/1995 Sue Mi

ARG>>>>>

[Ruegen]

Few ways to do this one is to make a custom query field in your query

NewField: iif(isnull([Field]),0,[Field])

- this will turn any null field into 0 or if it is a string field "nothing" and thus show as it won't be null

If you search this forum you should find some other solutions (even I have posts where I wasn't able to see null rows)

[ItsMe]

You are not going to be able to assign 0 to a date field. Have you tried using the criteria option while in design view of a query object? If you add criteria Is Null in the grid at the bottom of the Designer Window, it will only retrieve the records that are Null for that Column. You can include additional columns in your criteria by adding Is Null to the respective column(s).

Here is the caveat. Adding criteria to multiple columns can employ the OR operator as well as the AND operator. When adding criteria to the grid, adding to the same row will employ the AND operator. You can employ the OR operator by dropping down one row from the previous. It sounds like you want to use different rows when adding your criteria to the grid.