Problem with IIf in a SELECT statement for a Form.

[Russell Pascoe]

Hi!

I am trying to influence the outcome of a subform through the use of a Combo
Box in te form, and the following statement;

SELECT Tenants.* FROM Tenants WHERE (((Tenants.[Moved
Out])=IIf([Period]="Current",Tenants.[Moved Out] Is Null,Tenants.[Moved
Out])));

This is not producing the result I want. In essence it the combo bos has two
states, "All" and "Current", so I figured that the IIf statement would yield
the required filter. If the combo box ("Period") = "Current" then the tenants
will not have moved out, so [Moved Out] Is Null etc

When I run this, I get NO results for "Current" and I get all the records
EXCEPT the current tenants when I select "All"

I know the problem lies with my Iif statement, but I can't see what I have
done wrong. Your observations will be gratefully received!

Thanks
Russell.

 

[Douglas J. Steele]

You're trying to change the Tenants.[Moved Out]=something to Tenants.[Moved
Out] Is Null via an IIf statement. You can't do that.

Try:

SELECT Tenants.* FROM Tenants
WHERE ([Period]="Current" AND [Moved Out] Is Null)
OR ([Period] <> "Current")