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"?
19  2049 
"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"?
The "*" character is a wildcard. Remove those and the adjoining ampersands.
strFilter = "[Keywords] = [txtSearch]"
Keith. www.keithwilby.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.
"Keith Wilby" <he**@there.com > wrote in message
news:43******** **@glkas0286.gr eenlnk.net... "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"?
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 ;)
"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!). :-)
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(aKeyWord s)
If aKeyWords(z) = KeyWord Then
KeyWordFilter = True
End If
Next z
End Function
Sub test()
Forms!Customers .Filter = "KeyWordFilter( 'Anders',Contac tName)"
Forms!Customers .FilterOn = True
End Sub
It is likely you would modify this to be:
Forms!FrmSuppli ersAll.Filter = "KeyWordFilter( txtSearch,Keywo rds)"
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
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.
"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.
"Chantelle" <sa*******@yaho o.com> wrote in message
news:11******** *************@g 43g2000cwa.goog legroups.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='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')
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. 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: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
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: 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: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
