Re: Skipping detail lines in a report

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

I'll try clarify...
Assuming the Northwind database with a new table (tblFilterWords) with a
text field (FilterWord). Add records for "ave" and "rd".

Then create a query with SQL like:

SELECT Employees.*
FROM Employees
WHERE (Select Count(*) from tblFilterWords where [Address] like "*" &
[FilterWord] & "*")=0;

This result set will not include any records from the Employees table where
any of the filter words are located anywhere in the Address field.

No code, no muss, just SQL.
--
Duane Hookom
MS Access MVP


"Dennis" <Dennis@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:E427092A-F66A-41D0-8E71-50CC64773658@xxxxxxxxxxxxxxxx
> I'll try & clarify.
>
> I have a table with entries in it like: "hotfix", "visio", "update" and
> the
> like. There are more, but you get the idea. The user may add new text,
> delete
> existing entries, or change the wording in an entry.
>
> I load these values into an array at startup. At report-time, I examine
> the
> text in a single field (column to the young guys ;^) ). If the filter text
> is
> anywhere in the column, that detail record must be skipped. For example:
>
> filter-text: "hotfix"
>
> Detail text: "Microsoft windows hotfix A476586"
>
> Since the detail line (column) contains the filter word, that line is not
> shown on the report. I spin through the entire array of filter-words (it's
> in
> a memory array) for each line. I'm not sure how to do it more efficiently,
> but the report is quite small, with only about 60 entries max per key.
>
> "Duane Hookom" wrote:
>
>> I think you can do this with SQL. Can you explain "ANY PART of the
>> detail-line's text" and "detail field"? Are you search for your filter
>> words
>> in more than one field in the report's record source?
>>
>> --
>> Duane Hookom
>> MS Access MVP
>> --
>>
>> "Dennis" <Dennis@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>> news:ECA96106-3048-4314-AC1E-9C5387404079@xxxxxxxxxxxxxxxx
>> > Okay, it's like this:
>> >
>> > I have a table in which resides a series of "filter words". It the
>> > filter
>> > word is in ANY PART of the detail-line's text, that detail line must be
>> > skipped. The user can add/remove/edit these filter words at any time,
>> > so
>> > coding a query isn't feasible (I think). I could have 10 of these, or
>> > 100.
>> > So
>> > I pass each detail line through a code fragment that checks the array
>> > of
>> > filter words (which I load at the start of the application). If any of
>> > that
>> > text is within the detail field, I skip the line.
>> >
>> > Hope that helps.
>> >
>> >
>> >
>> > "Duane Hookom" wrote:
>> >
>> >> Why does your record source contain the records that you don't want to
>> >> show?
>> >>
>> >> --
>> >> Duane Hookom
>> >> MS Access MVP
>> >>
>> >>
>> >> "Dennis" <Dennis@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>> >> news:C1A2C733-E1ED-432A-BA67-45E0DCDB17B2@xxxxxxxxxxxxxxxx
>> >> > Hi! Running Access 2002.
>> >> >
>> >> > I've designed a report (actually, it's a parent with two
>> >> > side-by-side
>> >> > sub-reports). The sub-report on the right has detail code behind it,
>> >> > in
>> >> > order
>> >> > to NOT display detail lines which match certain criteria. That line
>> >> > is
>> >> > set
>> >> > to
>> >> > "Detail.Visible = False"
>> >> >
>> >> > The problem is that vertical space is reserved for the missing
>> >> > (invisible)
>> >> > lines, thus creating a BLANK page 2. What I need to do is ignore the
>> >> > specified lines completely, and not reserve space at all. That will
>> >> > give
>> >> > me
>> >> > the one-page report I need.
>> >> >
>> >> > How can I do this please?
>> >> >
>> >> > TIA!
>> >>
>> >>
>> >>
>>
>>
>>


.

Relevant Pages

  • Re: Skipping detail lines in a report
    ... saying the SQL is too complex. ... > any of the filter words are located anywhere in the Address field. ... I spin through the entire array of filter-words (it's ... >> but the report is quite small, with only about 60 entries max per key. ...
    (microsoft.public.access.reports)
  • Variable Arg Lists to PreparedStatements
    ... Is there a way to use PreparedStatements with SQL ... have a PreparedStatement retrieve all of them in a single SQL call. ... SELECT FROM employees WHERE employee_type =? ... one option where you first insert your array of IDs into a Temp table ...
    (comp.lang.java.programmer)
  • Variable Arg Lists to PreparedStatements
    ... Is there a way to use PreparedStatements with SQL ... have a PreparedStatement retrieve all of them in a single SQL call. ... SELECT FROM employees WHERE employee_type =? ... one option where you first insert your array of IDs into a Temp table ...
    (comp.lang.java.databases)
  • Re: pl/sql: display employees who hired in specific month
    ... So far, when I run my script, all employees (including emp who are ...    END LOOP; ... SQL> -- Your code, using EMP table ... SQL> SET SERVEROUTPUT ON ...
    (comp.databases.oracle.server)
  • Re: Self-join question
    ... Thank you, Roel. ... > I don't think you can do that with SQL Server's SQL dialect. ... > FROM Employees ... > EmployeeID ManagerID Name ...
    (microsoft.public.sqlserver.programming)