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
  #1  
Old May 24th, 2010, 07:43 PM posted to microsoft.public.access.queries
sebastico
external usenet poster
 
Posts: 74
Default parameter for text

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
  #2  
Old May 24th, 2010, 08:26 PM posted to microsoft.public.access.queries
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default parameter for text

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

  #3  
Old May 24th, 2010, 09:31 PM posted to microsoft.public.access.queries
sebastico
external usenet poster
 
Posts: 74
Default parameter for text


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

  #4  
Old May 24th, 2010, 10:00 PM posted to microsoft.public.access.queries
Bob Barrows
external usenet poster
 
Posts: 475
Default parameter for text

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


  #5  
Old May 24th, 2010, 10:24 PM posted to microsoft.public.access.queries
PieterLinden via AccessMonster.com
external usenet poster
 
Posts: 307
Default parameter for text

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

  #6  
Old May 24th, 2010, 10:36 PM posted to microsoft.public.access.queries
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default parameter for text

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

  #7  
Old May 25th, 2010, 02:33 AM posted to microsoft.public.access.queries
sebastico
external usenet poster
 
Posts: 74
Default parameter for text


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

  #8  
Old May 25th, 2010, 02:38 AM posted to microsoft.public.access.queries
sebastico
external usenet poster
 
Posts: 74
Default parameter for text


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


.

  #9  
Old May 25th, 2010, 02:40 AM posted to microsoft.public.access.queries
sebastico
external usenet poster
 
Posts: 74
Default parameter for text


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

.

  #10  
Old May 25th, 2010, 05:48 AM posted to microsoft.public.access.queries
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default parameter for text

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

 




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:22 PM.


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