Query Trouble

  • Thread starter Thread starter Eoin McGlynn
  • Start date Start date
E

Eoin McGlynn

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 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".
 
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
Click to expand...

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

= [forms]![frmGroupEmail]![emailfilter] OR
([forms]![frmGroupEmail]![emailfilter] = "All Grades" AND [Email] IS NOT NULL)
 
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])
 
Thanks, you're a genius!

KARL DEWEY said:
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 said:
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
Click to expand...
Click to expand...
 
Back
Top