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"?
Feb 6 '06
19  2051 
"paii, Ron" <pa**@packairin c.com> wrote in message
news:da******** ************@at henet.net...
"Anthony England" <ae******@oops. co.uk> wrote in message
news:ds******** **@nwrdmz03.dmz .ncs.ea.ibs-infra.bt.com...
"Chantelle" <sa*******@yaho o.com> wrote in message
news:11******** *************@g 14g2000cwa.goog legroups.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|transpor t|agriculture|s chools|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|transpor t|agriculture|s chools|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.
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?
"Lyle Fairfield" <ly***********@ aim.com> wrote in message
news:11******** *************@g 14g2000cwa.goog legroups.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='exerci se') AND
ConID IN (SELECT KeyConID FROM tblKeyword WHERE Keyword='fitnes s') AND
ConID NOT IN (SELECT KeyConID FROM tblKeyword WHERE Keyword='campin g')
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.
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
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
pa***********@l ibero.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(Cance l As Integer)
Dim r As ADODB.Recordset
Set r = New ADODB.Recordset
With r
.ActiveConnecti on = CurrentProject. Connection
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockBatchOpti mistic
.Open "SELECT * FROM Customers WHERE
KeyWordFilter(' Anders',Contact Name)"
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
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(Cance l As Integer)
Dim r As ADODB.Recordset
Set r = New ADODB.Recordset
With r
.ActiveConnecti on = CurrentProject. Connection
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockBatchOpti mistic
.Open "SELECT * FROM Customers WHERE
KeyWordFilter(' Anders',Contact Name)"
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********@Fort uneJames.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.
"Lyle Fairfield" <ly***********@ aim.com> wrote in message
news:11******** *************@g 14g2000cwa.goog legroups.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='200 507') AND
ConID IN (SELECT CctConID FROM tblConCat WHERE CctCatCode='200 508'))
OR
(ConType='L' AND
ConID IN (SELECT CctConID FROM tblConCat WHERE CctCatCode='200 102') AND
ConID IN (SELECT CctConID FROM tblConCat WHERE CctCatCode='200 107'));
' List Approach:
' *************** *
SELECT tblContact.ConI D, tblContact.ConN ame
FROM tblContact
WHERE (ConType='L' AND
InStr(1,ConSubj ectList,',20050 7,',0)>0 AND
InStr(1,ConSubj ectList,',20050 8,',0)>0)
OR
(ConType='L' AND
InStr(1,ConSubj ectList,',20010 2,',0)>0 AND
InStr(1,ConSubj ectList,',20010 7,',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.
These results are quite different. Could you zip a db with the two
tables (tblContact and tblConCat) and post or e-mail them?
"Lyle Fairfield" <ly***********@ aim.com> wrote in message
news:11******** **************@ g43g2000cwa.goo glegroups.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. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: Charles Hartman |
last post by:
I'm working on text-handling programs that want plain-text files as
input. It's fine to tell users to feed the programs with plain-text
only, but not all users know what this means, even after you explain
it, or they forget. So it would be nice to be able to handle gracefully
the stuff that MS Word (or any word-processor) puts into a file.
Inserting a 0-127 filter is easy but not very friendly. Typically, the
w.p. file loads OK (into a...
|
by: antonovaCCCS |
last post by:
In our old VB 6 application we utilize a mail merge feature of Word 97 with
MS Access. A table in Access is populated from VB and a Word template is
wired to that table. So all we do in the code is call the merge method of
Word.
Problem:
We are moving to VB.NET (VS 2005) and Office 2003. I want to bypass Access
and either use a connection to SQL or a parametrized stored procedure. I
found some samples, but was not able to open the...
|
by: leeonions |
last post by:
Hi there,
i am trying to use regular expressions to search through a text string
and replace a given whole word.
take the string = "The matsat on the mat!" (bad example i know)
i want to replace the whole word 'mat' with the word 'cat' to give
"The matsat on the cat!"
(matsat was not replaced as only whole word match, cat on the other
|
by: leeonions |
last post by:
Hi there,
i am trying to use regular expressions to search through a text string
and replace a given whole word.
take the string = "The matsat on the mat!" (bad example i know)
i want to replace the whole word 'mat' with the word 'cat' to give
"The matsat on the cat!"
(matsat was not replaced as only whole word match, cat on the other
|
by: |
last post by:
Hello,
Does anyone have an idea on how I can filter the data in the gridview
control that was returned by an sql query?
I have a gridview that works fine when I populate it with data. Now I want
to look at that data and filter it based on what is in it.
I know that this could have been done with data sets and data views in
asp.net 1.1 but how is this done now in asp.net 2.0?
| | |
by: Steven |
last post by:
Hello,
I have a question about strcmp(). I have four words, who need to be
compared if it were two strings. I tried adding the comparison values
like '(strcmp(w1, w2) + strcmp(w3, w4))', where w1 and w2 make up the
first string and, w3 and w4 make up the second string. I do not want
to allocate memory, then put the words together to create a string
only to facilitate strcmp() comparison.
My question; Does anyone know how to get the...
|
by: seannakasone |
last post by:
Is there a way to search a string for a whole word w/o using a regular
expression?
i.e.
mystring.IndexOf(mypattern, *WholeWord);
The reason i rather not use a regular expression is because sometimes i
want to search for whole words w/o the regular expression special
characters being interpreted.
|
by: Tyler |
last post by:
Hello All:
I am currently working on a project to create an FEM model for school.
I was thinking about using wxPython to gather the 12 input variables
from the user, then, after pressing the "Run" button, the GUI would
close, and the 12 input variables would then be available for the rest
of the program.
So far, what I have been able to do is mostly a reverse engineering
job to get the frame to look right and return the text variable...
|
by: bogorman |
last post by:
I am using the following coding:
Dim strWordDocFile As System.String = Me.txtLetter.Text
Dim WordDocFile As New System.IO.FileInfo(strWordDocFile)
Dim Msg As String
Dim strDatafile As String
Dim strQueryString As String
Dim strAlphFldName As String
Dim strFilterValue As String
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| | |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| | |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
