Iff & Not Equal to criteria

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
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?
--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


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

Sin said:
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
Click to expand...
IIf([Forms]![frReportFilter]![ReportList]="Report1" said:
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?
Click to expand...
 
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.


Jeff Boyce said:
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?
--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


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

Sin said:
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
Click to expand...
IIf([Forms]![frReportFilter]![ReportList]="Report1" said:
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?
Click to expand...
Click to expand...
 
The method works well. Thank you.

John Spencer said:
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"


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

Sin said:
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?
Click to expand...
Click to expand...
 
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.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


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

Sin said:
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.


Jeff Boyce said:
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?
--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


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

for
all
Click to expand...
IIf([Forms]![frReportFilter]![ReportList]="Report1" said:
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
Click to expand...
hundreds
of
data in the data table). Could anyone tell me where I went wrong in my
condition?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top