IIF Statement not accepting ‘or’ condition or ‘Like’

[Monib]

Hello All,

I am trying to create an IIF statement which checks the combo box in a form. The Combo box has three options male, female & either. If the combo displays either I want all records to display, otherwise just select the gender chosen in the combo box.

I have tried using an or statement:
IIf([Forms]![Assignment]![Gender]="Either","male" Or "female",[Forms]![Assignment]![Gender])

I have tried using like:
IIf([Forms]![Assignment]![Gender]="Either",Like "*",[Forms]![Assignment]![Gender])

I have also just tried *:
IIf([Forms]![Assignment]![Gender]="Either","*",[Forms]![Assignment]![Gender])

In all cases the false condition works fine but when the statement is true, nothing is displayed.

Any help, tips or suggestions are much appreciated

Moni

 

[CJ_London]

where are you using the iif statement? in a query or vba code? or vba code to build a query?

 

[Monib]

Hello CJ_London, I am trying to use the iif statement in a query

 

[Galaxiom]

Iif statements can only be use to configure values in the query. They cannot be used to define the comparison operators.

 

[Monib]

So does that mean that it isn’t possible to get an iif statement to display all the records in a table?

 

[CJ_London]

2 things, you cannot include '=', 'like' etc in your criteria in the way and if you look how 'and' and 'or' is written in a criteria it would be myfld=1 or myfld=2 (not myfld=1 or 2)

try something like

isTrue: iif(IIf([Forms]![Assignment]![Gender]="Either",True,myfield=[Forms]![Assignment]![Gender])

and in your criteria

isTrue=True

 

[Galaxiom]

try something like

isTrue: iif(IIf([Forms]![Assignment]![Gender]="Either",True,myfield=[Forms]![Assignment]![Gender])

and in your criteria

isTrue=True

That won't work because Where conditions cannot be applied to a derived field in the same query. It would require the IIF to be repeated in the Where condition or use a subquery. (I have not contemplated the logic itself.)

BTW

WHERE  isTrue=True

The comparison is redundant.

With boolean values one simply needs:

WHERE isTrue

 

[Monib]

Thanks CJ_London! That worked I was trying to put the if statement in the criteria of thefild but having the statement itself as a field like you suggested totally worked.

 

[Galaxiom]

Thanks CJ_London! That worked I was trying to put the if statement in the criteria of thefild but having the statement itself as a field like you suggested totally worked.

Ah yes. I have been writing queries in the SQL View too much.

I forgot that the Design View automatically manages the repeat of the derived field in the SQL Where clause.

It would require the IIF to be repeated in the Where condition or use a subquery.

 

[Simon_MT]

I put these type of filtering before the form and filter the records once a choose has been made:

Two combis Gender and Age

Private Function SearchCriteria() As String
    With CodeContextObject
        If Not IsNull(.Gender) Then
            SearchCriteria = "[Gender] = '" & .Gender & "'"
        ElseIf .Age <> 0 And IsNull(.Gender) Then
             SearchCriteria = "[Age] = " & .[Age]
        Else
             SearchCriteria = SearchCriteria & " And [Age] = " & .[Age]
       End If
    End With
End Function

Simon