On my main form, I have an option group (ACDFrame), with two choices: 1 and 2 (default value)

I have a button on the main form that opens Form 2.

If 1, I want to show all the records in the next form I open.

If 2, I want to show ONLY the records with NULL data in the field in question.

Here is what I have under the field (add chg del) in the query that Form 2 is linked to:

IIf([Forms]![Form - Master]![ACDFrame]=2,[Static TBL 0000010028 BOM May 10]![add chg del]='Is Null',[Static TBL 0000010028 BOM May 10]![add chg del])

My problem is that the True part of the IIf structure ([Static TBL 0000010028 BOM May 10]![add chg del]='Is Null') doesnt bring out the null records, it shows none.

The False part works.

Help anyone??

You can't do what you are trying to do since queries cannot be modified "on the fly" this way. What is actually appening with the code as you have it is Access has generated a where clause like the following:

WHERE [Static TBL 0000010028 BOM May 10].[add chg del]= SomeValue

The IIf() in the true case is placing the text string "IsNull" in SomeValue which is of course why you are not retrieving any data. In the false case it is placing the value of [Static TBL 0000010028 BOM May 10].[add chg del] itself in SomeValue. So since a field compared to itself would always test true, all rows are being returned.

If I understand your question, you really want
WHERE [Static TBL 0000010028 BOM May 10].[add chg del] Is Null

Or no where clause at all.

So remove the criteria from the query and try this in the click event of the button where the form is being opened instead:

If Me.[ACDFrame] = 2 Then
DoCmd.OpenForm..... ~with no filter specified
Else
DoCmd.OpenForm...... ~with a filter
End If

Replace the DoCmd.OpenForm.....'s with your actual open form command and in the second case specify a filter.

By the way, IsNull is a VBA function and Is Null is an SQL expression. In neither case would they be surrounded by quotes since they are both language elements. Only text variables are ever surrounded with quotes.

PS - please do not post the same message in multiple places.

[This message has been edited by Pat Hartman (edited 06-15-2001).]