report filter changes

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

Guest

I have created a report which uses the following filter:

[PROGRAM] like [Type Program (CON, MKT, etc)] and [PROGRAM]<>"" and
[category]="03" or [PROGRAM] like [Type Program (CON, MKT, etc)] and
[PROGRAM]<>"" and [category]="02" or [PROGRAM] like [Type Program (CON, MKT,
etc)] and [PROGRAM]<>"" and [category]="31" or [PROGRAM] like [Type Program
(CON, MKT, etc)] and [PROGRAM]<>"" and [category]="31DRGM"

For a few days, the report works fine with this filter, giving the user the
option of selecting which "program" to pull (ADM, CIS, CON, INT, MED, MKT,
RCH, RRL, RVP, SPT, TDS) and returning all expected information in the
report. However, after a few days, I get a call from another employee, saying
that they are no longer being prompted to enter which program they want to
pull into this report. I go into the design of the report, and it now shows
the filter as:

[program] = "ADM"

The "ADM" program is the first alphabetically of all the programs, and the
report is sorted alpha by program. But why in the world is my filter
modifying itself to only pull the records for the program that appears first
alphabetically? At this point I have to paste the original filter back into
the design of the report, knowing that I'll get another call in week.

I'm reaching for straws...This report returns approximately 1,000+ records.
Is there some maximum number of records that can be returned that may make
this filter modify itself?

Any help would be much appreciated. - C. Mellow

Version Microsoft Access 2000 (9.0.6926 SP-3)
 
There are several ways the report's Filter property can be overwritten.
Merely using OpenReport with a WhereCondition will change the Filter.

Create a query as the source for the report. Move the expression into the
WHERE clause of the query.
 
cmellow said:
I have created a report which uses the following filter:

[PROGRAM] like [Type Program (CON, MKT, etc)] and [PROGRAM]<>"" and
[category]="03" or [PROGRAM] like [Type Program (CON, MKT, etc)] and
[PROGRAM]<>"" and [category]="02" or [PROGRAM] like [Type Program (CON, MKT,
etc)] and [PROGRAM]<>"" and [category]="31" or [PROGRAM] like [Type Program
(CON, MKT, etc)] and [PROGRAM]<>"" and [category]="31DRGM"

For a few days, the report works fine with this filter, giving the user the
option of selecting which "program" to pull (ADM, CIS, CON, INT, MED, MKT,
RCH, RRL, RVP, SPT, TDS) and returning all expected information in the
report. However, after a few days, I get a call from another employee, saying
that they are no longer being prompted to enter which program they want to
pull into this report. I go into the design of the report, and it now shows
the filter as:

[program] = "ADM"

The "ADM" program is the first alphabetically of all the programs, and the
report is sorted alpha by program. But why in the world is my filter
modifying itself to only pull the records for the program that appears first
alphabetically? At this point I have to paste the original filter back into
the design of the report, knowing that I'll get another call in week.

I'm reaching for straws...This report returns approximately 1,000+ records.
Is there some maximum number of records that can be returned that may make
this filter modify itself?

Any help would be much appreciated. - C. Mellow

Version Microsoft Access 2000 (9.0.6926 SP-3)
Click to expand...


That is weird, but then the Filter property is notorious for
its crazy antics. I gave up on it years ago in favor of
more traditional approaches.

The most common alternative is to use the report's record
source query to do the filtering in its WHERE clause.

If you are constructing the filter in code, then use the
report's Open event to construct the report query's SQL
statement and add the filtering expression in the
statement's WHERE clause. Then assign the SQL string to the
report's RecordSource property.


Note that your filtering expression is unnecessarily
complicated and can be reduced to:

[PROGRAM] Like [Type Program (CON, MKT, etc)] And
[PROGRAM]<>"" And [category] IN("03","02","31","31DRGM")

Also, unless you expect users to enter a wildcard pattern in
the parameter prompt, I think you should use = instead of
Like, but this is a minor point.
 
Back
Top