A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

parameter for text



 
 
Thread Tools Display Modes
  #11  
Old May 25th, 2010, 12:36 PM posted to microsoft.public.access.queries
Bob Barrows
external usenet poster
 
Posts: 475
Default parameter for text

You will need a separate textbox for each pattern for which the user wishes
to search. This of course, will be problematic. If you provide two
textboxes, the users will tell you they need a third. After you provide that
one, they will of course demand a 4th ... ad nauseam

You might consider a keyword-table approach, especially if users will only
ever be searching for whole words. This will involve creating a table that
includes the key field(s) from the first table, plus a single field for the
keywords. You would probably want to exclude "nuisance" words from the
keyword list: "the", "to", "a", etc. Including them would cause too many
unrelated results to be returned in searches (Google has a similar
approach). The result would be like this:

Sourcetable:
ID textfield
1 A phrase with several words

Keywords:
ID Keyword
1 phrase
1 several
1 words

Your criterion would be in the ID field and would look like this*:
IN (Select ID FROM keywords WHERE "," & [txtwords] & "," LIKE "*," &
[keyword] & ",*" )

*This is untested and was included only to provide a general idea for how to
approach this.

sebastico wrote:
Bob

The field I'm searching has one or more words. Can I enter one, two
or three words or do I have to use more txtboxes?
Thank you for your help
"Bob Barrows" wrote:

sebastico wrote:
In Access 2003.
In a form I have a parameter to search for a word at a a time
Like [Froms].[Form].txtWords]&"*" which works well.
.
In order to enter more than two oarameter separated by commas in
txtwords I have been tryin the Str function like this
"Instr([txtWords])"
"Instr[txtWords]"

The query displays no records at all.

Could you suggest me how to do it

I don't think it's doable. If the field you were searching contained
only single words and you were trying to match the entire word, the
Instr solution can work. But since you are doing pattern-matching
using LIKE, there is no way to search for multiple patterns without
using OR
to combine them:

Like [txtword1] & "*" OR Like [txtword2] & "*"

It appears you will need to provide multiple textboxes and instruct
the users to enter a single search pattern into each.

--
HTH,
Bob Barrows


.


--
Microsoft MVP - ASP/ASP.NET - 2004-2007
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 09:29 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.