Prev: Populate Table with Fields from Another Table
Next: SQL Statement Insert Into
From: Ray on 12 Jan 2010 10:42
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?
From: vanderghast on 12 Jan 2010 11:03
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

"Ray" <Ray(a)discussions.microsoft.com> wrote in message
news:D3833C8C-B966-4A54-B931-81F6675D5F1E(a)microsoft.com...
>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?

From: John W. Vinson on 12 Jan 2010 12:21
On Tue, 12 Jan 2010 07:42:01 -0800, Ray <Ray(a)discussions.microsoft.com> wrote:

>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")

--

John W. Vinson [MVP]
From: Ray on 12 Jan 2010 15:09
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.

"Ray" wrote:

> 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?
 | 
Pages: 1
Prev: Populate Table with Fields from Another Table
Next: SQL Statement Insert Into