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

Many thanks

I would suggest separating by space and using this --
Like "*" & [Froms].[Form].txtWords] &"*"

Remember that when you type in 'bell' you will also get 'bellows' in the
results.

--
Build a little, test a little.


"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
>
> Many thanks

Karl

Thanks for your reply.
I'm sorry. I wrote the instruction wrong. It must be:
Like [Forms].[Form].[txtWords]&"*"
Nevertheless, as you suggested I tested again:

Like "*" & [Forms].[Form].[txtWords] &"*" .
If I enter one word it works but If I enter two words separated by space,
query shows nothing. I don't know why.

Any idea is welcome


"KARL DEWEY" wrote:

> I would suggest separating by space and using this --
> Like "*" & [Froms].[Form].txtWords] &"*"
>
> Remember that when you type in 'bell' you will also get 'bellows' in the
> results.
>
> --
> Build a little, test a little.
>
>
> "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
> >
> > Many thanks

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

I missed this one.

Place [Forms].[Form].[txtWords] in the field row of the query design grid.

In the criteria row put --
Like "*" & [YourTableName].[FieldBeingSearched] &"*"


--
Build a little, test a little.


"sebastico" wrote:

>
> Karl
>
> Thanks for your reply.
> I'm sorry. I wrote the instruction wrong. It must be:
> Like [Forms].[Form].[txtWords]&"*"
> Nevertheless, as you suggested I tested again:
>
> Like "*" & [Forms].[Form].[txtWords] &"*" .
> If I enter one word it works but If I enter two words separated by space,
> query shows nothing. I don't know why.
>
> Any idea is welcome
>
>
> "KARL DEWEY" wrote:
>
> > I would suggest separating by space and using this --
> > Like "*" & [Froms].[Form].txtWords] &"*"
> >
> > Remember that when you type in 'bell' you will also get 'bellows' in the
> > results.
> >
> > --
> > Build a little, test a little.
> >
> >
> > "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
> > >
> > > Many thanks

Karl
With your parameter:
If I enter one word (any stored in the table), the parameter displays all
records (same as I have in the table).
If I enter two words displays all records as well.


"KARL DEWEY" wrote:

> I missed this one.
>
> Place [Forms].[Form].[txtWords] in the field row of the query design grid.
>
> In the criteria row put --
> Like "*" & [YourTableName].[FieldBeingSearched] &"*"
>
>
> --
> Build a little, test a little.
>
>
> "sebastico" wrote:
>
> >
> > Karl
> >
> > Thanks for your reply.
> > I'm sorry. I wrote the instruction wrong. It must be:
> > Like [Forms].[Form].[txtWords]&"*"
> > Nevertheless, as you suggested I tested again:
> >
> > Like "*" & [Forms].[Form].[txtWords] &"*" .
> > If I enter one word it works but If I enter two words separated by space,
> > query shows nothing. I don't know why.
> >
> > Any idea is welcome
> >
> >
> > "KARL DEWEY" wrote:
> >
> > > I would suggest separating by space and using this --
> > > Like "*" & [Froms].[Form].txtWords] &"*"
> > >
> > > Remember that when you type in 'bell' you will also get 'bellows' in the
> > > results.
> > >
> > > --
> > > Build a little, test a little.
> > >
> > >
> > > "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
> > > >
> > > > Many thanks

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

Pieter
Could you explain in more detail your suggestion?

Thank you.
"PieterLinden via AccessMonster.com" 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
> >
> >Many thanks
>
> You wouldn't be able to do this directly if you want to search for multiple
> words
> You could use SPLIT to break out the separate words (and maybe use TRIM to
> cut off the leading and trailing space). Then you would have to loop through
> the array and AND them to the filter. Not trivial, but doable.
>
> --
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/For...eries/201005/1
>
> .
>

Post the SQL of your query by opening in design view, click on VIEW - SQL
View, highlight all, copy, and paste in a post.

--
Build a little, test a little.


"sebastico" wrote:

>
> Karl
> With your parameter:
> If I enter one word (any stored in the table), the parameter displays all
> records (same as I have in the table).
> If I enter two words displays all records as well.
>
>
> "KARL DEWEY" wrote:
>
> > I missed this one.
> >
> > Place [Forms].[Form].[txtWords] in the field row of the query design grid.
> >
> > In the criteria row put --
> > Like "*" & [YourTableName].[FieldBeingSearched] &"*"
> >
> >
> > --
> > Build a little, test a little.
> >
> >
> > "sebastico" wrote:
> >
> > >
> > > Karl
> > >
> > > Thanks for your reply.
> > > I'm sorry. I wrote the instruction wrong. It must be:
> > > Like [Forms].[Form].[txtWords]&"*"
> > > Nevertheless, as you suggested I tested again:
> > >
> > > Like "*" & [Forms].[Form].[txtWords] &"*" .
> > > If I enter one word it works but If I enter two words separated by space,
> > > query shows nothing. I don't know why.
> > >
> > > Any idea is welcome
> > >
> > >
> > > "KARL DEWEY" wrote:
> > >
> > > > I would suggest separating by space and using this --
> > > > Like "*" & [Froms].[Form].txtWords] &"*"
> > > >
> > > > Remember that when you type in 'bell' you will also get 'bellows' in the
> > > > results.
> > > >
> > > > --
> > > > Build a little, test a little.
> > > >
> > > >
> > > > "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
> > > > >
> > > > > Many thanks

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"