Iff & Not Equal to criteria

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Sin - 30 Oct 2007 01:31 GMT

I have a table with data field year, state and value. I'm trying to run a
query for the table with criteria: If user select "report1" in the report
list, then exclude TAS from the data set, otherwise, include all data for all
other selection. The following is the criteria I've put into the query.

Like
IIf([Forms]![frReportFilter]![ReportList]="Report1",([DataTable].[State])<>"TAS","*")

The query runs fine for all other report, but everytime when I run
"report1", the query will return no data (although there are over hundreds of
data in the data table). Could anyone tell me where I went wrong in my
condition?

Reply to this Message

John Spencer - 30 Oct 2007 13:18 GMT

You cannot build the criteria like that.

Depending on the complexity of the criteria you could try entering the
following in the criteria "box" under the State field

<> "TAS" OR Forms]![frReportFilter]![ReportList]<>"Report1"

After you save the query that will get reorganized by Access.

If that is the only criteria in the query Access will add a new column
FIeld: Forms]![frReportFilter]![ReportList]
Criteria(1): <> "Report1"
Criteria(2): <<Blank>>

The State field will look like
Field: State
Criteria(1): <<Blank>>
Criteria(2): <> "TAS"

Signature

John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

>I have a table with data field year, state and value. I'm trying to run a
> query for the table with criteria: If user select "report1" in the report
[quoted text clipped - 10 lines]
> data in the data table). Could anyone tell me where I went wrong in my
> condition?

Reply to this Message

Sin - 30 Oct 2007 22:44 GMT

The method works well. Thank you.

> You cannot build the criteria like that.
>
[quoted text clipped - 29 lines]
> > data in the data table). Could anyone tell me where I went wrong in my
> > condition?

Reply to this Message

Jeff Boyce - 30 Oct 2007 13:20 GMT

Does it really matter whether TAS is included or not (in the query)?

If you are trying to generate a report, couldn't you simply leave TAS out of
the report1 (even if the query returned it)?

What am I missing?

Signature

Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP
http://mvp.support.microsoft.com/

Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

> I have a table with data field year, state and value. I'm trying to run a
> query for the table with criteria: If user select "report1" in the report
> list, then exclude TAS from the data set, otherwise, include all data for all
> other selection. The following is the criteria I've put into the query.
>
> Like

IIf([Forms]![frReportFilter]![ReportList]="Report1",([DataTable].[State])<>"
TAS","*")

> The query runs fine for all other report, but everytime when I run
> "report1", the query will return no data (although there are over hundreds of
> data in the data table). Could anyone tell me where I went wrong in my
> condition?

Reply to this Message

Sin - 30 Oct 2007 22:04 GMT

The data exported from the database froms the basis of another report in
which TAS must not be revealed and I don't want user to go through the
trobule of going through the exported data to delete all TAS record every
time they run the report.

> Does it really matter whether TAS is included or not (in the query)?
>
[quoted text clipped - 18 lines]
> > data in the data table). Could anyone tell me where I went wrong in my
> > condition?

Reply to this Message

Jeff Boyce - 31 Oct 2007 12:00 GMT

It may be a bit of a "brute force" approach, but you could create a new
query based on the one that does return TAS, and in the new query, leave it
out. Export the new query.

Signature

Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP
http://mvp.support.microsoft.com/

Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

> The data exported from the database froms the basis of another report in
> which TAS must not be revealed and I don't want user to go through the
[quoted text clipped - 14 lines]
> > >
> > > Like

IIf([Forms]![frReportFilter]![ReportList]="Report1",([DataTable].[State])<>"

> > TAS","*")
> > >
[quoted text clipped - 3 lines]
> > > data in the data table). Could anyone tell me where I went wrong in my
> > > condition?

Reply to this Message