Passing multiple strings from a text box to a select query

[Guest]

Hello,

First, let me thank everyone who has contributed to these forums, you have
all helped me create a great database! Currently I'm at a standstill though.
I've searched the forums and tried to adapt the information provided in the
post "Use wildcard in combo box with multiple strings" however my query turns
up empty.

I'm trying to find a way to pass multiple strings from a textbox
(txtPartName) on my main form (frmCodeSearch) to my select query. The query
results are then displayed in a sub form. I'm able to enter a single string
that is either a full or partial word/phrase and use the criteria Like "*" &
[Forms]![frmCodeSearch]![txtPartName] & "*" for the field (PartName) from the
table tblParts. This allows me to enter "seat" in the text box and return
all records where the part name contains the word seat (i.e. seatbelt, seat
cushion, seat back, driver's seat, etc.).

Is there a way to enter multiple strings in the txtPartName textbox and pass
them all to the query? I would like to be able to enter "seat, driver" and
have the query return all records where the PartName field contains both
words. Thanks in advance to anyone who is able to shed some light on my
conundrum!

CCorreia

 

[Allen Browne]

See:
Use a multi-select list box to filter a report
at:
http://allenbrowne.com/ser-50.html

The article explains how to create the string to use as the WhereCondition
for OpenReport. You could use an identical string as the Filter of a form.
It is not simple to do that in a query though.

 

[Guest]

Hi Allen,

Thanks for answering my question. Both the code you reference and the
Search Criteria database page on your website have given me lots of new
things to think about.

I'm not sure I understand the method you have provided for resolving this
issue and I'm hoping you'll provide me with a little more insight. As I
understand your post you suggest that a WhereCondition can be built in the
same manner and then the form filter can be pointed to my unbound textbox.
I'm still playing around with the code you have provided to make this work
(if I'm headed in the right direction). The questions I currently have are:

1) Will a filtered form allow a subform that displays records from a query
to only display those records that match the filter criteria? (Currently all
records are returned if a textbox's criteria =Null)
2) Does the code you provide allow for wildcards to be included for each
string? (I assume this would be another part added in before the delimiter
",")
3) Can the code provided be placed in the txtPartName_AfterUpdate Event and
if so, how does the form then reapply the filter once new search criteria is
inputted?
4) I achieved the results I am looking for by changing the criteria in the
query from

Like "*" & [Forms]![frmCodeSearch]![txtPartName] & "*"

to

Like "*" & "First Term" & "*" AND Like "*" & "Second Term" & "*"

However in this manner a user would have to go into the query and specify
First Term, Second Term, etc. each time. Can the WhereCondition your code
builds be passed to the query criteria using the
[Forms]![frmCodeSearch]![txtPartName] parameter? Again, thanks for taking
the time to educate those of us who are learning to use Access one mistake at
a time!

CCorreia

See:
Use a multi-select list box to filter a report
at:
http://allenbrowne.com/ser-50.html

The article explains how to create the string to use as the WhereCondition
for OpenReport. You could use an identical string as the Filter of a form.
It is not simple to do that in a query though.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hello,

First, let me thank everyone who has contributed to these forums, you have
all helped me create a great database! Currently I'm at a standstill
though.
I've searched the forums and tried to adapt the information provided in
the
post "Use wildcard in combo box with multiple strings" however my query
turns
up empty.

I'm trying to find a way to pass multiple strings from a textbox
(txtPartName) on my main form (frmCodeSearch) to my select query. The
query
results are then displayed in a sub form. I'm able to enter a single
string
that is either a full or partial word/phrase and use the criteria Like "*"
&
[Forms]![frmCodeSearch]![txtPartName] & "*" for the field (PartName) from
the
table tblParts. This allows me to enter "seat" in the text box and return
all records where the part name contains the word seat (i.e. seatbelt,
seat
cushion, seat back, driver's seat, etc.).

Is there a way to enter multiple strings in the txtPartName textbox and
pass
them all to the query? I would like to be able to enter "seat, driver"
and
have the query return all records where the PartName field contains both
words. Thanks in advance to anyone who is able to shed some light on my
conundrum!

CCorreia

 

[Allen Browne]

Replies in-line with your questions.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen,

Thanks for answering my question. Both the code you reference and the
Search Criteria database page on your website have given me lots of new
things to think about.

I'm not sure I understand the method you have provided for resolving this
issue and I'm hoping you'll provide me with a little more insight. As I
understand your post you suggest that a WhereCondition can be built in the
same manner and then the form filter can be pointed to my unbound textbox.

Yes, that's correct. The "Trouble-shooting and Extending" section has the
one line you need to apply it to OpenReport.

I'm still playing around with the code you have provided to make this work
(if I'm headed in the right direction). The questions I currently have
are:

1) Will a filtered form allow a subform that displays records from a query
to only display those records that match the filter criteria? (Currently
all
records are returned if a textbox's criteria =Null)

Yes: The code just ignores any boxes that are null. That's a much more
efficient result than the convoluted alternatives shown under "Using a query
instead."

2) Does the code you provide allow for wildcards to be included for each
string? (I assume this would be another part added in before the delimiter
",")

The Name text box in the example uses wildcards for a text box.

The code for the list box uses the IN operator which does not support
wildcards. If you wanted to use wildcards with the list box, you would need
to change the code to use the OR operator. But if the list box contians a
list of actual values (as it normally does), the wildcard probably is not
needed.

3) Can the code provided be placed in the txtPartName_AfterUpdate Event
and
if so, how does the form then reapply the filter once new search criteria
is
inputted?

Yes, it could be. Of course, if you have multiple unbound boxes where the
user enters criteria, you would need to call it in the AfterUpdate of each
one.

4) I achieved the results I am looking for by changing the criteria in the
query from

Like "*" & [Forms]![frmCodeSearch]![txtPartName] & "*"

to

Like "*" & "First Term" & "*" AND Like "*" & "Second Term" & "*"

Those expression don't work correctly. They don't return the rows where the
fields are null (blank, no entry.)

However in this manner a user would have to go into the query and specify
First Term, Second Term, etc. each time. Can the WhereCondition your code
builds be passed to the query criteria using the
[Forms]![frmCodeSearch]![txtPartName] parameter?

You can, using a huge convoluted query statement as shown towards the end of
the article under:
Using a query instead

Again, thanks for taking
the time to educate those of us who are learning to use Access one mistake
at
a time!

CCorreia

See:
Use a multi-select list box to filter a report
at:
http://allenbrowne.com/ser-50.html

The article explains how to create the string to use as the
WhereCondition
for OpenReport. You could use an identical string as the Filter of a
form.
It is not simple to do that in a query though.

Hello,

First, let me thank everyone who has contributed to these forums, you
have
all helped me create a great database! Currently I'm at a standstill
though.
I've searched the forums and tried to adapt the information provided in
the
post "Use wildcard in combo box with multiple strings" however my query
turns
up empty.

I'm trying to find a way to pass multiple strings from a textbox
(txtPartName) on my main form (frmCodeSearch) to my select query. The
query
results are then displayed in a sub form. I'm able to enter a single
string
that is either a full or partial word/phrase and use the criteria Like
"*"
&
[Forms]![frmCodeSearch]![txtPartName] & "*" for the field (PartName)
from
the
table tblParts. This allows me to enter "seat" in the text box and
return
all records where the part name contains the word seat (i.e. seatbelt,
seat
cushion, seat back, driver's seat, etc.).

Is there a way to enter multiple strings in the txtPartName textbox and
pass
them all to the query? I would like to be able to enter "seat, driver"
and
have the query return all records where the PartName field contains
both
words. Thanks in advance to anyone who is able to shed some light on
my
conundrum!

CCorreia