Query Help

[utellem]

I am creating a filter button with the following query...

Form_MemberHeading.Filter = "[Active] = -1 AND [Title] <> 'Missionary'"



I want to find all records where the ACTIVE is TRUE and the TITLE is not equal to the phrase MISSIONARY.
The button works except it also excludes the records where the TITLE is blank. How can I fix this?

Many thanks

[June7]

Try:

Form_MemberHeading.Filter = "[Active] = -1 AND [Title] <> 'Missionary' AND Not IsNull([Title])"

or

Form_MemberHeading.Filter = "[Active] = -1 AND [Title] <> 'Missionary' AND Not [Title] Is Null"

[utellem]

Thank you for the effort. I tried them both but I got the same results with both. All three lines pulled out the records with various titles but no records which had that field blank. Anymore ideas? Again, thank you.

[Dal Jeanis]

Form_MemberHeading.Filter = "([Active] = -1) AND ([Title] & '' <> 'Missionary')"

June - you forgot the parens around the title tests, should be OR null.

[June7]

Dal, why the & and extra "? Fix it and just puts OP back where they started.

But you are right about the parens and OR. I just had to read OP more carefully. I think Is Null is the correct syntax.

Form_MemberHeading.Filter = "[Active] = -1 AND ([Title] <> 'Missionary' Or [Title] Is Null)"

[utellem]

PERFECT!!! Many thank yous.

[Dal Jeanis]

June -

That's two single-quotes, so it's a zero-length string, and concatenated with Null it's still the empty string. I've often seen that done as a cheap way to eliminate Nulls. (Usually I use two double-quotes, but in situ single quotes were required.)

Since "Null & ''" is the empty string and is not equal to 'Missionary', that also merged the two tests into a single condition.