Prev: return product with maximum price
Next: How do I show all characters before the letter A in a Query?
From: J_Goddard via AccessMonster.com on 25 Mar 2010 16:17
Hi -

How are you using this SQL? Is it being assigned to a variable, i.e. strVar =
.... or being used in a command to open a recordset? If so, there is
something wrong with the arrangement of the quotation marks. Try this:

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

This assumes that ExType is the name of a field in the table tblExclude. If
ExType is a variable containing the name if a field, then try this:

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

HTH

John


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

--
John Goddard
Ottawa, ON Canada
jrgoddard at cyberus dot ca

Message posted via http://www.accessmonster.com

From: Brad on 26 Mar 2010 10:26
Thanks everyone for your replies.

1. I was hoping the astarisk would work like a wild card. I need to find the
values in the list within tblWebLog.[cs-user-agent]

2. The field is a Memo field, it's a very long string

3. tblExclude is a text field

4. I don't think I want to do a join as I need to include the wild card option

5. Example of what I'm trying to compare:

tblExclude = bot
tblWebLog = msnbot/1.0+(+http://search.msn.com/msnbot.htm)
=
NextGenSearchBot+1+(for+information+visit+http://www.zoominfo.com/About/misc/NextGenSearchBot.aspx)
=
Mozilla/4.0+(compatible;+MSIE+6.0;+Windows+NT+5.1;+SV1;+.NET+CLR+1.1.4322;+.NET+CLR+2.0.50727;+InfoPath.1)


So the result I'm looking for is to filter out the first two records, and
just leave the third, based on my subquery Not In(SQL). At the moment I have
a Criteria line in my query that looks like this:
Not Like "*bot*" And Not Like "*Crawl*" And Not Like "*walk*" And Not Like
"*ia_archiver*" And Not Like "*Java/*" And Not Like "*Spider*" And Not Like
"*al_Viewer*"

It works but it's not very dynamic. I'd like to just add a value to
tblExclude and then the query would be updated.

Thanks again for everyones replies

Brad

"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 26 Mar 2010 12:08
On Fri, 26 Mar 2010 07:26:12 -0700, Brad <Brad(a)discussions.microsoft.com>
wrote:

>Thanks everyone for your replies.
>
>1. I was hoping the astarisk would work like a wild card. I need to find the
>values in the list within tblWebLog.[cs-user-agent]
>
>2. The field is a Memo field, it's a very long string
>
>3. tblExclude is a text field
>
>4. I don't think I want to do a join as I need to include the wild card option
>
>5. Example of what I'm trying to compare:
>
>tblExclude = bot
>tblWebLog = msnbot/1.0+(+http://search.msn.com/msnbot.htm)
> =
>NextGenSearchBot+1+(for+information+visit+http://www.zoominfo.com/About/misc/NextGenSearchBot.aspx)
> =
>Mozilla/4.0+(compatible;+MSIE+6.0;+Windows+NT+5.1;+SV1;+.NET+CLR+1.1.4322;+.NET+CLR+2.0.50727;+InfoPath.1)
>

I'd suggest using a "Non Equi Join" frustrated outer join query then:

SELECT tblWebLog.[cs-user-agent]
FROM tblWebLog LEFT JOIN tblExclude
ON tblWebLog.[cs-user-agent] LIKE "*" & tblExclude.ExType & "*"
WHERE tblExclude.ExType IS NULL;

--

John W. Vinson [MVP]
From: John Spencer on 26 Mar 2010 12:10
You can do a join just not in query design view. You need a NON-Equi join
(one that does not use the = for the comparison).

Your query SQL might look something like the following.
SELECT tblWebLog.[cs-user-agent]
FROM tblWebLog LEFT JOIN tblExclude
ON tblWebLog.[cs-user-agent] Like "*" & tblExclude.EXtype & "*"
WHERE tblExclude.EXtype Is Null

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

Brad wrote:
> Thanks everyone for your replies.
>
> 1. I was hoping the astarisk would work like a wild card. I need to find the
> values in the list within tblWebLog.[cs-user-agent]
>
> 2. The field is a Memo field, it's a very long string
>
> 3. tblExclude is a text field
>
> 4. I don't think I want to do a join as I need to include the wild card option
>
> 5. Example of what I'm trying to compare:
>
> tblExclude = bot
> tblWebLog = msnbot/1.0+(+http://search.msn.com/msnbot.htm)
> =
> NextGenSearchBot+1+(for+information+visit+http://www.zoominfo.com/About/misc/NextGenSearchBot.aspx)
> =
> Mozilla/4.0+(compatible;+MSIE+6.0;+Windows+NT+5.1;+SV1;+.NET+CLR+1.1.4322;+.NET+CLR+2.0.50727;+InfoPath.1)
>
>
> So the result I'm looking for is to filter out the first two records, and
> just leave the third, based on my subquery Not In(SQL). At the moment I have
> a Criteria line in my query that looks like this:
> Not Like "*bot*" And Not Like "*Crawl*" And Not Like "*walk*" And Not Like
> "*ia_archiver*" And Not Like "*Java/*" And Not Like "*Spider*" And Not Like
> "*al_Viewer*"
>
> It works but it's not very dynamic. I'd like to just add a value to
> tblExclude and then the query would be updated.
>
> Thanks again for everyones replies
>
> Brad
>
> "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: Brad on 26 Mar 2010 13:07
You guys must be working side by side!! Same answer. Thanks so very much for
your help. I've never heard of that kind of a join before. I'll read up on it
and learn more.

I tried your query, but it is not working, cs-user-agent is a memo field, so
I guess I can't do any actions on it. I has to be a memo field as the data I
put in that field is really long.

I suppose I'm out of luck?

Thanks again,

Brad

"Brad" wrote:

> Thanks everyone for your replies.
>
> 1. I was hoping the astarisk would work like a wild card. I need to find the
> values in the list within tblWebLog.[cs-user-agent]
>
> 2. The field is a Memo field, it's a very long string
>
> 3. tblExclude is a text field
>
> 4. I don't think I want to do a join as I need to include the wild card option
>
> 5. Example of what I'm trying to compare:
>
> tblExclude = bot
> tblWebLog = msnbot/1.0+(+http://search.msn.com/msnbot.htm)
> =
> NextGenSearchBot+1+(for+information+visit+http://www.zoominfo.com/About/misc/NextGenSearchBot.aspx)
> =
> Mozilla/4.0+(compatible;+MSIE+6.0;+Windows+NT+5.1;+SV1;+.NET+CLR+1.1.4322;+.NET+CLR+2.0.50727;+InfoPath.1)
>
>
> So the result I'm looking for is to filter out the first two records, and
> just leave the third, based on my subquery Not In(SQL). At the moment I have
> a Criteria line in my query that looks like this:
> Not Like "*bot*" And Not Like "*Crawl*" And Not Like "*walk*" And Not Like
> "*ia_archiver*" And Not Like "*Java/*" And Not Like "*Spider*" And Not Like
> "*al_Viewer*"
>
> It works but it's not very dynamic. I'd like to just add a value to
> tblExclude and then the query would be updated.
>
> Thanks again for everyones replies
>
> Brad
>
> "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
First  |  Prev  |  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?