field Is Null/Is Not Null criterion and IIf function

[Guest]

I would like to formulate a query that will determine if the criterion of a
field is to be Is Null or Is Not Null depending on the data of another field
in equijoined table. I am trying to use the IIf function to build this
criterion in design view using:

IIf([Other_Table]![Determining_Field]="X",Is Not Null, Is Null)

However, the SQL query I get is:
WHERE ...
([This_Table]![This_Field]=(IIf([Other_Table]![Determining_Field]="X",Is Not
Null, Is Null)) ...

which is obviously not what I want to determine but...

WHERE ...
([This_Table]![This_Field] IS {NOT} NULL) ...

Could you please advise me the syntax error that is occuring?

 

[Marshall Barton]

I would like to formulate a query that will determine if the criterion of a
field is to be Is Null or Is Not Null depending on the data of another field
in equijoined table. I am trying to use the IIf function to build this
criterion in design view using:

IIf([Other_Table]![Determining_Field]="X",Is Not Null, Is Null)

However, the SQL query I get is:
WHERE ...
([This_Table]![This_Field]=(IIf([Other_Table]![Determining_Field]="X",Is Not
Null, Is Null)) ...

which is obviously not what I want to determine but...

WHERE ...
([This_Table]![This_Field] IS {NOT} NULL) ...

Could you please advise me the syntax error that is occuring?

IIf([Other_Table]![Determining_Field]="X",
[This_Table]![This_Field] Is Not Null,
[This_Table]![This_Field] Is Null)

 

[Guest]

I would like to formulate a query that will determine if the criterion of a
field is to be Is Null or Is Not Null depending on the data of another field
in equijoined table. I am trying to use the IIf function to build this
criterion in design view using:

IIf([Other_Table]![Determining_Field]="X",Is Not Null, Is Null)

However, the SQL query I get is:
WHERE ...
([This_Table]![This_Field]=(IIf([Other_Table]![Determining_Field]="X",Is Not
Null, Is Null)) ...

which is obviously not what I want to determine but...

WHERE ...
([This_Table]![This_Field] IS {NOT} NULL) ...

Could you please advise me the syntax error that is occuring?

IIf([Other_Table]![Determining_Field]="X",
[This_Table]![This_Field] Is Not Null,
[This_Table]![This_Field] Is Null)

Hi - that is what I originally figured was the answer to my problem... but
I keep getting the error: "The expression is typed incorrectly or it is too
complex to be evaluated"

Reviewing the SQL command that corresponds to the design view, I am still
getting
WHERE ..
([This_Table]![This_Field]=(IIf([Other_Table]![Determining_Field]="X",[This_Table]![This_Field] Is Not
Null, [This_Table]![This_Field]Is Null)) ...

 

[Marshall Barton]

I would like to formulate a query that will determine if the criterion of a
field is to be Is Null or Is Not Null depending on the data of another field
in equijoined table. I am trying to use the IIf function to build this
criterion in design view using:

IIf([Other_Table]![Determining_Field]="X",Is Not Null, Is Null)

However, the SQL query I get is:
WHERE ...
([This_Table]![This_Field]=(IIf([Other_Table]![Determining_Field]="X",Is Not
Null, Is Null)) ...

which is obviously not what I want to determine but...

WHERE ...
([This_Table]![This_Field] IS {NOT} NULL) ...

Could you please advise me the syntax error that is occuring?

IIf([Other_Table]![Determining_Field]="X",
[This_Table]![This_Field] Is Not Null,
[This_Table]![This_Field] Is Null)

Hi - that is what I originally figured was the answer to my problem... but
I keep getting the error: "The expression is typed incorrectly or it is too
complex to be evaluated"

Reviewing the SQL command that corresponds to the design view, I am still
getting
WHERE ...
([This_Table]![This_Field]=(IIf([Other_Table]![Determining_Field]="X",[This_Table]![This_Field] Is Not
Null, [This_Table]![This_Field]Is Null)) ...

This is one of those cases where the QBE just gets in the
way. Switch to SQL view and edit the where condition to get
rid of the extraneous
[This_Table]![This_Field]=