Query filter isn't filtering?

[ConfusedDatabaseDesigner]

Hello,



I'm creating a query from two tables, one parent and one child. I created a filter on a field taken from the child table (a date field, if that matters) and put "Is Null" (I didn't actually type in the "" symbols, though) in the query criteria box of this field so that only records with a blank value in that field will show up. However, when I run the query, there's a record that appears with date in that field. What's up with that? Could it be that records that are not null are appearing anyways if they have a 'sister' record that is null (that is, they share the same parent record)? Is this fixable?

Thanks in advance for your help!

[Jester0001]

I just tried to replicate this using two tables (a parent and child), and filtering on a date field where it Is Null. I couldn't replicate the problem you described. Could you provide more info, such as the SQL, screenshots, or uploading the db for more analysis?

[ConfusedDatabaseDesigner]

At the moment I can't provide you with a copy of the database because it contains confidential information. I've copy/pasted the SQL info here, but if that's not helpful let me know and I'll try to provide a data-less version of the database when I can. I should warn you that unfortunately I know next to nothing of coding, SQL, VBA, and general database design, so this is probably very sloppy. If it is confusing or doesn't make sense, let me know and I'll try to explain what I'm trying to achieve as best as I can.

SELECT [Participants - Basic Info].[Participant ID] AS [Participants - Basic Info_Participant ID], [Participants - Basic Info].[First Name], [Participants - Basic Info].[Last Name], [Participants - Basic Info].DOB, [Participants - Basic Info].Gender, [Participants - Basic Info].[Permitted to contact?], [Participants - Basic Info].[If not, why?], [Participants - Basic Info].[Recruited From], [Participants - In Lab].[Participation ID], [Participants - In Lab].[Participant ID] AS [Participants - In Lab_Participant ID], [Participants - In Lab].Project, [Participants - In Lab].[Last contacted on], [Participants - In Lab].[Last contacted by (netid)], [Participants - In Lab].[Participation Date], Round(((Date()-[DOB])/365.25),2) AS Age
FROM [Participants - Basic Info] LEFT JOIN [Participants - In Lab] ON [Participants - Basic Info].[Participant ID] = [Participants - In Lab].[Participant ID]
WHERE ((([Participants - Basic Info].[Permitted to contact?])="Only email contact") AND (([Participants - In Lab].[Participation Date]) Is Null)) OR ((([Participants - Basic Info].[Permitted to contact?])="Not yet")) OR ((([Participants - Basic Info].[Permitted to contact?])="Permitted"));

Thanks SO much for your time, it is truly appreciated!!

[Kirsti]

Hi there,

In your WHERE clause, you have:

(([Participants - In Lab].[Participation Date]) Is Null)) OR ((([Participants - Basic Info].[Permitted to contact?])="Not yet")) OR ((([Participants - Basic Info].[Permitted to contact?])="Permitted"));

Should you be using And instead of Or?

If [Permitted to contact?] ="Not yet" in a particular record, then that record will be returned whether [Participation Date] Is Null or Is Not Null. Likewise if [Permitted to contact?]="Permitted".

Hope this helps.

[ConfusedDatabaseDesigner]

Thanks so much, Kirsti! It works very well now

[Kirsti]

Glad to hear it.