Prev: return product with maximum price
Next: How do I show all characters before the letter A in a Query?
From: Brad on 25 Mar 2010 13:23
Thanks for taking the time to read my question.

I want to use a field in a table as a list of items to filter out in a query.

I have this:

SELECT tblWebLog.[cs-user-agent]
FROM tblWebLog
WHERE (((tblWebLog.[cs-user-agent]) Not In (SELECT DISTINCTROW "*" & ExType
& "*" FROM tblExclude)));


but I get the following error:

Invalid Memo, OLE, or Hyperlink Object in subquery
'tblWebLog.[cs-user-agent]'.

I'm not sure what I'm doing wrong. Any suggestions?
Thanks,

Brad
From: KARL DEWEY on 25 Mar 2010 14:31
Try this --
SELECT tblWebLog.[cs-user-agent]
FROM tblWebLog LEFT JOIN tblExclude ON tblWebLog.[cs-user-agent] =
tblExclude.ExType
WHERE tblExclude.ExType Is Null;

--
Build a little, test a little.


"Brad" wrote:

> Thanks for taking the time to read my question.
>
> I want to use a field in a table as a list of items to filter out in a query.
>
> I have this:
>
> SELECT tblWebLog.[cs-user-agent]
> FROM tblWebLog
> WHERE (((tblWebLog.[cs-user-agent]) Not In (SELECT DISTINCTROW "*" & ExType
> & "*" FROM tblExclude)));
>
>
> but I get the following error:
>
> Invalid Memo, OLE, or Hyperlink Object in subquery
> 'tblWebLog.[cs-user-agent]'.
>
> I'm not sure what I'm doing wrong. Any suggestions?
> Thanks,
>
> Brad
From: John Spencer on 25 Mar 2010 14:40
I see no reason to use DistinctRow in the subquery

SELECT tblWebLog.[cs-user-agent]
FROM tblWebLog
WHERE tblWebLog.[cs-user-agent]
Not In (SELECT "*" & ExType & "*"
FROM tblExclude)

That assumes that the field contents of tblWebLog.[cs-user-agent] that you
want to exclude starts and ends with an asterisk.

It is possible that something has corrupted the query. The easiest way to fix
this is to open a NEW query and paste the SQL statement into the SQL window.
Then try to run the new query.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Brad wrote:
> Thanks for taking the time to read my question.
>
> I want to use a field in a table as a list of items to filter out in a query.
>
> I have this:
>
> SELECT tblWebLog.[cs-user-agent]
> FROM tblWebLog
> WHERE (((tblWebLog.[cs-user-agent]) Not In (SELECT DISTINCTROW "*" & ExType
> & "*" FROM tblExclude)));
>
>
> but I get the following error:
>
> Invalid Memo, OLE, or Hyperlink Object in subquery
> 'tblWebLog.[cs-user-agent]'.
>
> I'm not sure what I'm doing wrong. Any suggestions?
> Thanks,
>
> Brad
From: John W. Vinson on 25 Mar 2010 15:19
On Thu, 25 Mar 2010 10:23:01 -0700, Brad <Brad(a)discussions.microsoft.com>
wrote:

>Thanks for taking the time to read my question.
>
>I want to use a field in a table as a list of items to filter out in a query.
>
>I have this:
>
>SELECT tblWebLog.[cs-user-agent]
>FROM tblWebLog
>WHERE (((tblWebLog.[cs-user-agent]) Not In (SELECT DISTINCTROW "*" & ExType
>& "*" FROM tblExclude)));
>
>
>but I get the following error:
>
>Invalid Memo, OLE, or Hyperlink Object in subquery
>'tblWebLog.[cs-user-agent]'.
>
>I'm not sure what I'm doing wrong. Any suggestions?
>Thanks,
>
>Brad

Are cs-user-agent or ExType Memo fields?
--

John W. Vinson [MVP]
From: John W. Vinson on 25 Mar 2010 15:33
On Thu, 25 Mar 2010 10:23:01 -0700, Brad <Brad(a)discussions.microsoft.com>
wrote:

>Thanks for taking the time to read my question.
>
>I want to use a field in a table as a list of items to filter out in a query.
>
>I have this:
>
>SELECT tblWebLog.[cs-user-agent]
>FROM tblWebLog
>WHERE (((tblWebLog.[cs-user-agent]) Not In (SELECT DISTINCTROW "*" & ExType
>& "*" FROM tblExclude)));

Additional thought: Are you assuming that the asterisks will work as
wildcards? Because they won't, in an IN() clause - only in a LIKE.

What's in the two fields? Could you give an example?
--

John W. Vinson [MVP]
 |  Next  |  Last
Pages: 1 2 3
Prev: return product with maximum price
Next: How do I show all characters before the letter A in a Query?