Not In (SELECT DISTINCTROW

[Brad]

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

 

[KARL DEWEY]

Try this --
SELECT tblWebLog.[cs-user-agent]
FROM tblWebLog LEFT JOIN tblExclude ON tblWebLog.[cs-user-agent] =
tblExclude.ExType
WHERE tblExclude.ExType Is Null;

 

[John Spencer]

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

 

[John W. Vinson]

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]

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?

 

[J_Goddard via AccessMonster.com]

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

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

 

[Brad]

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

 

[John W. Vinson]

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 Spencer]

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

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

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

 

[Brad]

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

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

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 Spencer]

It should work. What error or bad result are you seeing/

Can you cut and paste the exact SQL that you are using.

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

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

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

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 W. Vinson]

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.

Durn, you're right: can't Join on a memo.

I think you'll need some VBA code to parse this out: e.g.

Public Function IsExcluded(strIn As String) As Boolean
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set rs = db.OpenRecordset("SELECT Extype FROM tblExclude", dbOpenSnapshot)
IsExcluded = False
Do Until rs.EOF
If InStr(rs!Extype, strIn) > 0 Then
IsExcluded = True
Exit Function
End If
rs.MoveNext
Loop
End Function

Then in your query use a calculated field

ExcludeMe: IsExcluded([cs-user-agent])

with a criterion of False.

This is going to be really really slow though!!

 

[David W. Fenton]

SELECT DISTINCTROW "*" & ExType
& "*" FROM tblExclude)));

DISTINCTROW serves no useful purpose when there's only one table
involved -- the above should be changed to SELECT DISTINCT.

 

[David W. Fenton]

SELECT DISTINCTROW "*" & ExType
& "*" FROM tblExclude)));

Also, you should be returning a single field in this -- even if
tblExclude.* returns only a single field, it's probably better to
specify that single field in the SELECT statement, in order to be
easier on the query optimizer.

 

[David W. Fenton]

Durn, you're right: can't Join on a memo.

You can't use an EXPLICIT join (i.e., use JOIN in the FROM clause)
but you can use an implicit join, i.e., using the WHERE clause. It
may cause truncation of values in the result that is returned,
though, so to get the actual memo you may need two instances of the
table with the memo in it.