Return whole word for filtering

[Chantelle]

I've got this A2K DB that has a continuous form that lists Suppliers
and their details. The form has a field for each supplier that holds
several Keywords that reflect the suppliers products or services.
I am new to writing VB but managed to create a command button that
filters the form based on a keyword that I type into a text box . The
code I have is :-

Dim strFilter As String
strFilter = "([Keywords] Like '*" & [txtSearch] & "*')"
Forms![FrmSuppliersAll].Filter = strFilter
Forms![FrmSuppliersAll].FilterOn = True

This filters the suppliers form OK but it also gives results where the
keyword may be part of a larger word eg say I want to filter on
"Sport", it will return sports, transport, sporting, etc.

Question is, is there any way to modify the code so that only only
records withthe Keyword itself is returned ie "Sport"?

[Keith Wilby]

"Chantelle" <sa*******@yahoo.com> wrote in message
news:11*********************@g14g2000cwa.googlegro ups.com...

I've got this A2K DB that has a continuous form that lists Suppliers
and their details. The form has a field for each supplier that holds
several Keywords that reflect the suppliers products or services.
I am new to writing VB but managed to create a command button that
filters the form based on a keyword that I type into a text box . The
code I have is :-

Dim strFilter As String
strFilter = "([Keywords] Like '*" & [txtSearch] & "*')"
Forms![FrmSuppliersAll].Filter = strFilter
Forms![FrmSuppliersAll].FilterOn = True

This filters the suppliers form OK but it also gives results where the
keyword may be part of a larger word eg say I want to filter on
"Sport", it will return sports, transport, sporting, etc.

Question is, is there any way to modify the code so that only only
records withthe Keyword itself is returned ie "Sport"?

The "*" character is a wildcard. Remove those and the adjoining ampersands.

strFilter = "[Keywords] = [txtSearch]"

Keith.
www.keithwilby.com

[Anthony England]

"Chantelle" <sa*******@yahoo.com> wrote in message
news:11*********************@g14g2000cwa.googlegro ups.com...

I've got this A2K DB that has a continuous form that lists Suppliers
and their details. The form has a field for each supplier that holds
several Keywords that reflect the suppliers products or services.
I am new to writing VB but managed to create a command button that
filters the form based on a keyword that I type into a text box . The
code I have is :-

Dim strFilter As String
strFilter = "([Keywords] Like '*" & [txtSearch] & "*')"
Forms![FrmSuppliersAll].Filter = strFilter
Forms![FrmSuppliersAll].FilterOn = True

This filters the suppliers form OK but it also gives results where the
keyword may be part of a larger word eg say I want to filter on
"Sport", it will return sports, transport, sporting, etc.

Question is, is there any way to modify the code so that only only
records withthe Keyword itself is returned ie "Sport"?

Well you could look for " sport " and the spaces would ensure you only got
occurrences of the keyword, but in typical sentences you might not find
spaces surrounding the word, e.g. punctuation or at the start of the field:
I like sport, but I'm lazy.
Sport isn't for me.
I don't do sport!
etc
So it's not going to be that easy if the text is unstructured. Of course,
if the text in the filed was of a reliable format, such that keywords were
guaranteed to be like:
|sport|transport|agriculture|schools|prisons|
Then you could search for |sport| and that would guarantee a match.

Better still would be to re-structure the database so that there was a
related table of keywords with an index placed on this field. You would
then find that searches became very much quicker and you could perform
queries not easily possible with the other format.

[Anthony England]

"Keith Wilby" <he**@there.com> wrote in message
news:43**********@glkas0286.greenlnk.net...

"Chantelle" <sa*******@yahoo.com> wrote in message
news:11*********************@g14g2000cwa.googlegro ups.com...

I've got this A2K DB that has a continuous form that lists Suppliers
and their details. The form has a field for each supplier that holds
several Keywords that reflect the suppliers products or services.
I am new to writing VB but managed to create a command button that
filters the form based on a keyword that I type into a text box . The
code I have is :-

Dim strFilter As String
strFilter = "([Keywords] Like '*" & [txtSearch] & "*')"
Forms![FrmSuppliersAll].Filter = strFilter
Forms![FrmSuppliersAll].FilterOn = True

This filters the suppliers form OK but it also gives results where the
keyword may be part of a larger word eg say I want to filter on
"Sport", it will return sports, transport, sporting, etc.

Question is, is there any way to modify the code so that only only
records withthe Keyword itself is returned ie "Sport"?

The "*" character is a wildcard. Remove those and the adjoining
ampersands.

strFilter = "[Keywords] = [txtSearch]"

Keith.
www.keithwilby.com

Keith - Last time you promised you were going to stop trying to read in
fast-forward mode ;)

[Keith Wilby]

"Anthony England" <ae******@oops.co.uk> wrote in message
news:ds**********@nwrdmz02.dmz.ncs.ea.ibs-infra.bt.com...

Keith - Last time you promised you were going to stop trying to read in
fast-forward mode ;)

You know, the moment I pressed 'send' and then walked away for a break I
knew I'd answered total crap, I do apologise (esp to the OP!). :-)

[Lyle Fairfield]

This works for the Customers form in Northwind. It is likely to require
some modification for your purposes. It may be too slow if your form
has a great number of records.

Public Function KeyWordFilter( _
ByVal KeyWord As String, _
ByVal KeyWords As String) As Boolean
Dim aKeyWords() As String
Dim z As Long
aKeyWords = Split(KeyWords, " ")
For z = 0 To UBound(aKeyWords)
If aKeyWords(z) = KeyWord Then
KeyWordFilter = True
End If
Next z
End Function

Sub test()
Forms!Customers.Filter = "KeyWordFilter('Anders',ContactName)"
Forms!Customers.FilterOn = True
End Sub

It is likely you would modify this to be:
Forms!FrmSuppliersAll.Filter = "KeyWordFilter(txtSearch,Keywords)"

[Chantelle]

Keith Wilby wrote:

"Anthony England" <ae******@oops.co.uk> wrote in message
news:ds**********@nwrdmz02.dmz.ncs.ea.ibs-infra.bt.com...

Keith - Last time you promised you were going to stop trying to read in
fast-forward mode ;)

You know, the moment I pressed 'send' and then walked away for a break I
knew I'd answered total crap, I do apologise (esp to the OP!). :-)

No worry. It has helped me understand much better how it works. What
I've done since is take out the first asterisk only and that way I now
get "sport" and "sports" which is better than I could have wished for.
Many thanks.

Chantelle

[Chantelle]

The Northwind solution looks very interesting and will take a bit more
time for me to get my head round it but I'm going to make a copy of the
DB and experiment with it.

As suggested by Anthony, it may have been better at the outset to have
had a table of key words but the consolation is that the "Suppliers"
side of the DB is not the main part. The DB is primarily a Contacts,
Events and Attendance. The "Suppliers" play a supporting role at some
of the events.

Incidentally, I came across a "Rolls Royce" solution to a similar
problem involving Patients and Symtoms where the Symtoms were featured
in a list box. That way a search could be based on multiple symptoms
selected from the list box by using the Ctrl key. I was full of
admiration for the approach of solution - but didn't quite understand
all the intracies of it.

Many thanks in meantime.

[paii, Ron]

"Anthony England" <ae******@oops.co.uk> wrote in message
news:ds**********@nwrdmz03.dmz.ncs.ea.ibs-infra.bt.com...

"Chantelle" <sa*******@yahoo.com> wrote in message
news:11*********************@g14g2000cwa.googlegro ups.com...

I've got this A2K DB that has a continuous form that lists Suppliers
and their details. The form has a field for each supplier that holds
several Keywords that reflect the suppliers products or services.
I am new to writing VB but managed to create a command button that
filters the form based on a keyword that I type into a text box . The
code I have is :-

Dim strFilter As String
strFilter = "([Keywords] Like '*" & [txtSearch] & "*')"
Forms![FrmSuppliersAll].Filter = strFilter
Forms![FrmSuppliersAll].FilterOn = True

This filters the suppliers form OK but it also gives results where the
keyword may be part of a larger word eg say I want to filter on
"Sport", it will return sports, transport, sporting, etc.

Question is, is there any way to modify the code so that only only
records withthe Keyword itself is returned ie "Sport"?

Well you could look for " sport " and the spaces would ensure you only got
occurrences of the keyword, but in typical sentences you might not find
spaces surrounding the word, e.g. punctuation or at the start of the

field: I like sport, but I'm lazy.
Sport isn't for me.
I don't do sport!
etc
So it's not going to be that easy if the text is unstructured. Of course,
if the text in the filed was of a reliable format, such that keywords were
guaranteed to be like:
|sport|transport|agriculture|schools|prisons|
Then you could search for |sport| and that would guarantee a match.

Better still would be to re-structure the database so that there was a
related table of keywords with an index placed on this field. You would
then find that searches became very much quicker and you could perform
queries not easily possible with the other format.

I use a keywords table for part descriptions. The parts form uses the table
to verify the free form description are entered in keyword format. It allows
very quick lookup/filtering by keyword while allowing for free form lookup
by description.

[Anthony England]

"Chantelle" <sa*******@yahoo.com> wrote in message
news:11*********************@g43g2000cwa.googlegro ups.com...

The Northwind solution looks very interesting and will take a bit more
time for me to get my head round it but I'm going to make a copy of the
DB and experiment with it.

As suggested by Anthony, it may have been better at the outset to have
had a table of key words but the consolation is that the "Suppliers"
side of the DB is not the main part. The DB is primarily a Contacts,
Events and Attendance. The "Suppliers" play a supporting role at some
of the events.

Incidentally, I came across a "Rolls Royce" solution to a similar
problem involving Patients and Symtoms where the Symtoms were featured
in a list box. That way a search could be based on multiple symptoms
selected from the list box by using the Ctrl key. I was full of
admiration for the approach of solution - but didn't quite understand
all the intracies of it.

Many thanks in meantime.

Although having a separate table is the 'proper relational' approach, there
are other downsides to it other than the added complexity. For example,
finding contacts who have all the keywords 'sport', 'exercise' and 'fitness'
but do not have the keyword 'camping'. You might write a query like this:

SELECT tblContact.* FROM tblContact WHERE
ConID IN (SELECT KeyConID FROM tblKeyword WHERE Keyword='sport') AND
ConID IN (SELECT KeyConID FROM tblKeyword WHERE Keyword='exercise') AND
ConID IN (SELECT KeyConID FROM tblKeyword WHERE Keyword='fitness') AND
ConID NOT IN (SELECT KeyConID FROM tblKeyword WHERE Keyword='camping')

Now although other systems (eg SQL Server) can do this pretty fast, it is
very slow using Access. And in this case you would be much faster searching
a single field of concatenated keywords.

And before someone else points it out, I know there are workarounds to this
such as building auxillary search tables, and these do speed things up, but
they also add to the complexity.

[Anthony England]

"paii, Ron" <pa**@packairinc.com> wrote in message
news:da********************@athenet.net...

"Anthony England" <ae******@oops.co.uk> wrote in message
news:ds**********@nwrdmz03.dmz.ncs.ea.ibs-infra.bt.com...

"Chantelle" <sa*******@yahoo.com> wrote in message
news:11*********************@g14g2000cwa.googlegro ups.com...

> I've got this A2K DB that has a continuous form that lists Suppliers
> and their details. The form has a field for each supplier that holds
> several Keywords that reflect the suppliers products or services.
>
>
> I am new to writing VB but managed to create a command button that
> filters the form based on a keyword that I type into a text box . The
> code I have is :-
>
> Dim strFilter As String
> strFilter = "([Keywords] Like '*" & [txtSearch] & "*')"
> Forms![FrmSuppliersAll].Filter = strFilter
> Forms![FrmSuppliersAll].FilterOn = True
>
> This filters the suppliers form OK but it also gives results where the
> keyword may be part of a larger word eg say I want to filter on
> "Sport", it will return sports, transport, sporting, etc.
>
> Question is, is there any way to modify the code so that only only
> records withthe Keyword itself is returned ie "Sport"?

Well you could look for " sport " and the spaces would ensure you only
got
occurrences of the keyword, but in typical sentences you might not find
spaces surrounding the word, e.g. punctuation or at the start of the

field:

I like sport, but I'm lazy.
Sport isn't for me.
I don't do sport!
etc
So it's not going to be that easy if the text is unstructured. Of
course,
if the text in the filed was of a reliable format, such that keywords
were
guaranteed to be like:
|sport|transport|agriculture|schools|prisons|
Then you could search for |sport| and that would guarantee a match.

Better still would be to re-structure the database so that there was a
related table of keywords with an index placed on this field. You would
then find that searches became very much quicker and you could perform
queries not easily possible with the other format.

I use a keywords table for part descriptions. The parts form uses the
table
to verify the free form description are entered in keyword format. It
allows
very quick lookup/filtering by keyword while allowing for free form lookup
by description.

I think that might be the best way forward for the OP - if you add a bit of
coding to the input form, you could do a number of things to that field such
as removing duplicates, removing blanks, trimming spaces, etc and making
sure that the keywords were then put back into the field properly, for
example:
|sport|transport|agriculture|schools|prisons|
This would allow queries such as Where InStr(1,MyField,"|sport|",1)>0 AND
InStr(1,MyField,"|leisure|",1)>0 etc and these could be quicker to write and
quicker to run than the related table approach.

[Lyle Fairfield]

Anthony England wrote:

This would allow queries such as Where InStr(1,MyField,"|sport|",1)>0 AND
InStr(1,MyField,"|leisure|",1)>0 etc and these could be quicker to write and
quicker to run than the related table approach.

Why would they be quicker to run? How do you know that? Where can we
see an example of the two approaches with the one being faster than the
other?

[Anthony England]

"Lyle Fairfield" <ly***********@aim.com> wrote in message
news:11*********************@g14g2000cwa.googlegro ups.com...

Anthony England wrote:

This would allow queries such as Where InStr(1,MyField,"|sport|",1)>0 AND
InStr(1,MyField,"|leisure|",1)>0 etc and these could be quicker to write
and
quicker to run than the related table approach.

Why would they be quicker to run? How do you know that? Where can we
see an example of the two approaches with the one being faster than the
other?

I will try to dig out the database. It actually consisted of about 30,000
libraries with about 100,000 records in the related subjects table. The job
was to build an interface where the users could select quite complicated
criteria such as "which libraries stock both (maths and physics) or (biology
and chemistry but not statistics)?"
Now as soon as you built up queries with these sort of criteria using the
structure I mentioned earlier:

<<contacts/keywords example is same principal as libraries/subjects>>
SELECT tblContact.* FROM tblContact WHERE
ConID IN (SELECT KeyConID FROM tblKeyword WHERE Keyword='sport') AND
ConID IN (SELECT KeyConID FROM tblKeyword WHERE Keyword='exercise') AND
ConID IN (SELECT KeyConID FROM tblKeyword WHERE Keyword='fitness') AND
ConID NOT IN (SELECT KeyConID FROM tblKeyword WHERE Keyword='camping')

These sort of queries ran terribly slowly. Whatever tricks I tried, the
machine would almost choke on this - taking more than 5 minutes to come back
to life. When the very same structure and queries were moved to SQL Server,
the whole thing was finished in about 1 second!

Out of curiosity I tried moving the subjects to a single "list of subjects"
text field and the results weren't quite as good as the SQL database, but
they were acceptable.

[pamelafluente]

Why don't you answer instead of making arrogant questions?

People come her to get answers, not to be questioned by you.

You fist answer, if you can. Then make questions.

-Pam

[pamelafluente]

How long would that take with say 500.000 recs? Have you estimated it?
And how can improve this solution? Why there is no error trapping? Is
this infallible? Making a linear search wouldn't be too naive?

-Pam

[Lyle Fairfield]

pa***********@libero.it wrote:

How long would that take with say 500.000 recs? Much too long.
Have you estimated it? Yes, as above.
And how can improve this solution?

Were the problem mine I would use a KeyWordsTable and a Table which
linked it to the Customers Table on their PK long integer IDs. Then I
would use a Join to identify the Customer Records with Key Word sport.
I suspect this would seem instantaneous.

Within the contraints given I would do !!!something like!!! this on
form open which
filters out 8192 of 745472 in 11 seconds on my laptop.

Private Sub Form_Open(Cancel As Integer)
Dim r As ADODB.Recordset
Set r = New ADODB.Recordset
With r
.ActiveConnection = CurrentProject.Connection
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockBatchOptimistic
.Open "SELECT * FROM Customers WHERE
KeyWordFilter('Anders',ContactName)"
End With
Set Me.Recordset = r
End Sub

Why there is no error trapping?
Because it is air code demonstrating a way to answer the original
poster's specific question. It is intended to be a preliminary
demonstration of an idea, not a mature solution.

Is this infallible?
Nothing is infallible.

Making a linear search wouldn't be too naive?
I suppose that would depend on whether or not the person scripting the
linear search could apply enough specificity to the problem to beat out
the advantages that the higher level split function might have in
operating at whatever level of sophistication it operates, clearly much
more "natively" than any VBA UDF could ever be. But perhaps?

Pam

[CDMAPoster]

Lyle Fairfield wrote:

Were the problem mine I would use a KeyWordsTable and a Table which
linked it to the Customers Table on their PK long integer IDs. Then I
would use a Join to identify the Customer Records with Key Word sport.
I suspect this would seem instantaneous.

Within the contraints given I would do !!!something like!!! this on
form open which
filters out 8192 of 745472 in 11 seconds on my laptop.

Private Sub Form_Open(Cancel As Integer)
Dim r As ADODB.Recordset
Set r = New ADODB.Recordset
With r
.ActiveConnection = CurrentProject.Connection
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockBatchOptimistic
.Open "SELECT * FROM Customers WHERE
KeyWordFilter('Anders',ContactName)"
End With
Set Me.Recordset = r
End Sub

Lyle,

Both the Split and table versions you presented are excellent ideas for
implementing keyword searches. They're much more elegant than my first
attempt at keyword searches several years ago. I speak from experience
when I say that keyword searches are more robust and much more
intuitive to end users than literal string matching. It seems to me
that the table version requires a little bit of supporting code to keep
the keywords current. The next time I do an e-commerce app I plan on
exploring your ideas in more detail.

James A. Fortune
CD********@FortuneJames.com

I didn't do the information web site at the main company I work for. A
customer worked out a deal where his son would produce the web site in
partial payment for services. I told the company that the political
expediency was O.K. as long as I get to do a separate one for customer
login that updates a SQL Server backend and provides dynamically
generated PDF's of job photos and percent completion status.

[Anthony England]

"Lyle Fairfield" <ly***********@aim.com> wrote in message
news:11*********************@g14g2000cwa.googlegro ups.com...

Anthony England wrote:

This would allow queries such as Where InStr(1,MyField,"|sport|",1)>0 AND
InStr(1,MyField,"|leisure|",1)>0 etc and these could be quicker to write
and
quicker to run than the related table approach.

Why would they be quicker to run? How do you know that? Where can we
see an example of the two approaches with the one being faster than the
other?

OK Lyle (if this still holds any interest)
The task is to find all libraries which stock either (maths and physics) or
(art and music) and although I realise that this involves the dreaded 'or',
it is only a single occuurence. Now, as discussed, we are comparing the
additional keyword table approach versus the single-table keyword list
approach.
' Keyword Approach:
' ****************
SELECT ConID, ConName
FROM tblContact
WHERE (ConType='L' AND
ConID IN (SELECT CctConID FROM tblConCat WHERE CctCatCode='200507') AND
ConID IN (SELECT CctConID FROM tblConCat WHERE CctCatCode='200508'))
OR
(ConType='L' AND
ConID IN (SELECT CctConID FROM tblConCat WHERE CctCatCode='200102') AND
ConID IN (SELECT CctConID FROM tblConCat WHERE CctCatCode='200107'));
' List Approach:
' ****************
SELECT tblContact.ConID, tblContact.ConName
FROM tblContact
WHERE (ConType='L' AND
InStr(1,ConSubjectList,',200507,',0)>0 AND
InStr(1,ConSubjectList,',200508,',0)>0)
OR
(ConType='L' AND
InStr(1,ConSubjectList,',200102,',0)>0 AND
InStr(1,ConSubjectList,',200107,',0)>0);

And the time taken to select about 900 matches from 30,000 records?
under 0.5 seconds for the list approach and over 140 seconds for the indexed
keyword table. And this wasn't even a complex query.

[Lyle Fairfield]

These results are quite different. Could you zip a db with the two
tables (tblContact and tblConCat) and post or e-mail them?

[Anthony England]

"Lyle Fairfield" <ly***********@aim.com> wrote in message
news:11**********************@g43g2000cwa.googlegr oups.com...

These results are quite different. Could you zip a db with the two
tables (tblContact and tblConCat) and post or e-mail them?

Sure - I'll strip out the superfluous stuff as the data obviously doesn't
belong to me.