Prev: Append query with username and date
Next: Need help with query summing hours
From: Eoin McGlynn on 23 Feb 2010 12:48
Hellow,

I'm using the filter below to filter a query. However when "All Grades" is
selected it will not display the "is not null" filtered query. It is balnk.
If I change is not null to one of the grades in use, the filter is applied.

=IIf([forms]![frmGroupEmail]![emailfilter]="All Grades", Is Not
Null,[forms]![frmGroupEmail]![emailfilter])

Access 2007, Vista
Thanks,
Eoin
From: ghetto_banjo on 23 Feb 2010 14:04
You actually want to do away with the iif statement altogether and
replace it with a simpler Boolean operation.

This is what you want for criteria i think, give it a try:


[Forms]![frmGroupEmail].[emailfilter] OR [Forms]![frmGroupEmail].
[emailfilter] = "All Grades"



It seems like an odd way of doing it, but when you actually look at
what this translates to in SQL, you will see how the Boolean OR
statement works to return all results when your filter = "All Grades".


From: John W. Vinson on 23 Feb 2010 14:10
On Tue, 23 Feb 2010 09:48:01 -0800, Eoin McGlynn
<EoinMcGlynn(a)discussions.microsoft.com> wrote:

>Hellow,
>
>I'm using the filter below to filter a query. However when "All Grades" is
>selected it will not display the "is not null" filtered query. It is balnk.
>If I change is not null to one of the grades in use, the filter is applied.
>
>=IIf([forms]![frmGroupEmail]![emailfilter]="All Grades", Is Not
>Null,[forms]![frmGroupEmail]![emailfilter])
>
>Access 2007, Vista
>Thanks,
>Eoin

You cannot use query operators such as IS NOT NULL, >, <, IN() etc. in IIF
statements - only the actual value.

Rather than using IIF at all, try a criterion such as

[EMail] = [forms]![frmGroupEmail]![emailfilter] OR
([forms]![frmGroupEmail]![emailfilter] = "All Grades" AND [Email] IS NOT NULL)

--

John W. Vinson [MVP]
From: KARL DEWEY on 23 Feb 2010 14:17
Is Not Null happens to be a function and you can not return such from an IIF
statement.
Try this --
Like IIf([forms]![frmGroupEmail]![emailfilter]="All Grades", "*",
[forms]![frmGroupEmail]![emailfilter])

--
Build a little, test a little.


"Eoin McGlynn" wrote:

> Hellow,
>
> I'm using the filter below to filter a query. However when "All Grades" is
> selected it will not display the "is not null" filtered query. It is balnk.
> If I change is not null to one of the grades in use, the filter is applied.
>
> =IIf([forms]![frmGroupEmail]![emailfilter]="All Grades", Is Not
> Null,[forms]![frmGroupEmail]![emailfilter])
>
> Access 2007, Vista
> Thanks,
> Eoin
From: Eoin McGlynn on 26 Feb 2010 14:56
Thanks, you're a genius!

"KARL DEWEY" wrote:

> Is Not Null happens to be a function and you can not return such from an IIF
> statement.
> Try this --
> Like IIf([forms]![frmGroupEmail]![emailfilter]="All Grades", "*",
> [forms]![frmGroupEmail]![emailfilter])
>
> --
> Build a little, test a little.
>
>
> "Eoin McGlynn" wrote:
>
> > Hellow,
> >
> > I'm using the filter below to filter a query. However when "All Grades" is
> > selected it will not display the "is not null" filtered query. It is balnk.
> > If I change is not null to one of the grades in use, the filter is applied.
> >
> > =IIf([forms]![frmGroupEmail]![emailfilter]="All Grades", Is Not
> > Null,[forms]![frmGroupEmail]![emailfilter])
> >
> > Access 2007, Vista
> > Thanks,
> > Eoin
 | 
Pages: 1
Prev: Append query with username and date
Next: Need help with query summing hours