IIF stmt in Criteria

[Ray]

I have a query and I have the following in the criteria of the ListOrder column

IIf([Forms]![SelCri]![HideNotActive]="Yes",<>45,[ListOrder])

I want the results to exclude only 45's from the ListOrder if the form's
control is set to "Yes"

Apparently the query does not like '<>45' in the iIIF statement. (It works
fine if the control is set to "No")

Is there a way to get the '<>45' to work in the IIF statement?

 

[vanderghast]

Bring the expression as a computed expression (first line of the query
designer):

iif( FORMS!SelCri!HideNotActive = "Yes", FieldName <> 45, FieldName =
ListOrder )

and under it, add the criteria
= true





Note that each argument must be fully evaluable. FieldName <> 45 is
fully evaluable, but <> 45 is not a complete expression fully evaluable.
ListOrder, alone, is also fully evaluable (it returns its value). So here,
the idea was to bring the comparision ( = or <> ) fully inside the iif, and
test the result of the iif (which is now a returned Boolean value).


Vanderghast, Access MVP

 

[John W. Vinson]

I have a query and I have the following in the criteria of the ListOrder column

IIf([Forms]![SelCri]![HideNotActive]="Yes",<>45,[ListOrder])

I want the results to exclude only 45's from the ListOrder if the form's
control is set to "Yes"

Apparently the query does not like '<>45' in the iIIF statement. (It works
fine if the control is set to "No")

Is there a way to get the '<>45' to work in the IIF statement?

Not really. But you can turn the logic around: use a criterion (in SQL view)
such as

WHERE ([Forms]![SelCri]![HideNotActive]="Yes" AND [ListOrder] <> 45) OR
([Forms]![SelCri]![HideNotActive] <> "Yes")

 

[Ray]

I appreciate the help. In this case I took the cowards way out. I made an
extra column in the table which has ListOrder and named it "Hide" and type is
Y/N. (-1 for Hide and 0 for Not Hide). The table has the following columns:
Classification, ListOrder, Hide etc. Added Hide to the query and did the
following to the Hide criteria .....

IIf([Forms]![SelCri]![HideNotActive]="Yes",0,[Hide])

This way people who are classified as Not Active (Hide = -1) will not appear.

Tested what both of you sent and getting close to making it work. Will save
your ideas for another time. Thanks for your efforts.