Here's the code I used. I added your select as an AND to the existing
WHERE.
It won't run, saying the SQL is too complex.
SELECT PC_Master_NATIV_SWARE_Extract_Table.PACKAGE_NAME,
PC_Master_NATIV_SWARE_Extract_Table.PACKAGE_VERS,
PC_Master_NATIV_SWARE_Extract_Table.ADD_DEL_FLAG,
qryCSID.TME_OBJECT_LABEL,
qryCSID.COMPUTER_SYS_ID
FROM PC_Master_Sware_Filter, (PC_Master_Extract_Table INNER JOIN
PC_Master_NATIV_SWARE_Extract_Table ON
PC_Master_Extract_Table.COMPUTER_SYS_ID =
PC_Master_NATIV_SWARE_Extract_Table.COMPUTER_SYS_ID) INNER JOIN qryCSID ON
PC_Master_Extract_Table.COMPUTER_SYS_ID = qryCSID.COMPUTER_SYS_ID
WHERE
(((qryCSID.TME_OBJECT_LABEL)=[Forms]![PC_Review_Admin_Mode]![frmObjTemp])
AND
(((select count(*) from [PC_Master_sware_filter] where
[PC_Master_NATIV_SWARE_Extract_Table.PACKAGE_NAME] like "*" &
[PC_Master_filter_word] & "*" = 0))<>False));
Duane Hookom said:
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 said:
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.
:
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
--
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.
:
Why does your record source contain the records that you don't want
to
show?
--
Duane Hookom
MS Access MVP
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!
