Prev: return product with maximum price
Next: How do I show all characters before the letter A in a Query?
From: John Spencer on 26 Mar 2010 15:03
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

Brad wrote:
> 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
From: John W. Vinson on 26 Mar 2010 15:33
On Fri, 26 Mar 2010 10:07:01 -0700, Brad <Brad(a)discussions.microsoft.com>
wrote:

>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!!

--

John W. Vinson [MVP]
From: David W. Fenton on 28 Mar 2010 18:48
=?Utf-8?B?QnJhZA==?= <Brad(a)discussions.microsoft.com> wrote in
news:7AA39964-4EA3-461A-B9FE-05372E9D5CEA(a)microsoft.com:

> 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 http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
From: David W. Fenton on 28 Mar 2010 18:49
=?Utf-8?B?QnJhZA==?= <Brad(a)discussions.microsoft.com> wrote in
news:7AA39964-4EA3-461A-B9FE-05372E9D5CEA(a)microsoft.com:

> 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 http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
From: David W. Fenton on 28 Mar 2010 18:53
John W. Vinson <jvinson(a)STOP_SPAM.WysardOfInfo.com> wrote in
news:pn2qq5h7vp6eb4tgb1jn1uhu82tb72bpur(a)4ax.com:

> 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.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
First  |  Prev  | 
Pages: 1 2 3
Prev: return product with maximum price
Next: How do I show all characters before the letter A in a Query?