iif Help Needed

[rkb0378]

I have a form that asks for the following input values.

date_in, date_out, and a check box for outstanding records.

When query is run if the outstanding check box is true I need all null values in date_out column. If false then all records should show.

Any ideas on the iif statement for the date_out criteria.

So far I can't get the iff statement to work with null values or all values.

Thanks

 

[PaulBricker]

Well, it would probably help to know a little more about your set up and what you are trying to accomplish. First, where are you using the iff statement. Also, are the date_in and date_out fields used in a Between..And statement or are you looking for specific records related to the date_in value. In the form opening a query with your query using criteria to pull down the form values?
Basically, if you could add as much info as possible, we might be able to get something going for you.

Paul

 

[rkb0378]

Paul,

Here is some more information about the setup. I am using a Between.. And statment for the date_in. In the date_out I had the iif statement.

Here is the between .. And statement (applyed to date_in column)

Between [Forms]![frmTeam3_Clientreport]![startdate] And [Forms]![frmTeam3_Clientreport]![enddate]

Here is my attempt at the iif statement. It is looking at a check box on the form.

iff ( Forms![frmTeam3_Clientreport]![chkOutstanding] = True, Is Null, )

If the check box is marked I want "Is null" to apply as criteia for date_out. If not checked the criteria should be blank.

I have tried many combinations and none have the correct results.

Thanks

Rob

 

[PaulBricker]

Well, this was not straightforward, but I think this will do it.
In a new column of your query put this expression on the Field line.

Expr2: IIf([Forms]![frmTeam3_Clientreport]![chkOutstanding] =-1,IIf(IsNull(date_out),1,0),0)

Then on the criteria line for this column put this expression.

IIf([Forms]![frmTeam3_Clientreport]![chkOutstanding]= -1,1,0)

This should do it for you.

Paul