Iff statement in a query

[watercooler]

I have a query that has a text field (Component) and a boolean field (bDozer). I want the query to filter according to a text box(txtType) on a seperate form (frmWork). So, within the query I put the following criteria under the bDozer column:

iff([forms]![frmWork]![txtType]="Dozer",True,False)

If I only use the [forms]![frmWork]![txtType]="Dozer" criteria it works just fine...but I will need the capability to hinge that value on a true/false criteria as my project continues. I keep getting a warning "Undefined expression 'iff' in expression". I've read through numerous threads and can't understand why I can't use this iff statement. Can anyone help?

 

[MajP]

You probably figured it out by now. You spelled it wrong. iif not iff.

 

[watercooler]

No, not until you mentioned it. Thanks so much MajP...I can't believe how much time that simple (and very stupid) mistake has cost me. Thanks again!

 

[myknlat]

Ok. That was the simple part. What if the form is not open and consequently not passing a value to the query. You get prompted to enter the value for the form and list or combo box. Is there a way that if the form is closed or is passing a null value to not prompt for the parameter value but prompt for more defined value?

Iff ([forms!testform!combo23] = Null, [Enter the value of the red box], [forms!testform!combo23]).

Basically, I'm looking to provide a userfriendly prompt rather that field name.

 

[dhookom]

You should not run the query without the form being opened. Your expression for the control being Null is close:

IIf (IsNull([forms!testform!combo23]), [Enter the value of the red box], [forms!testform!combo23])

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]