Passing multiple strings from a text box to a select query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

CCorreia - 09 Mar 2007 15:28 GMT

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

Reply to this Message

Allen Browne - 09 Mar 2007 15:49 GMT

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.

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Hello,
>
[quoted text clipped - 30 lines]
>
> CCorreia

Reply to this Message

CCorreia - 12 Mar 2007 21:40 GMT

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
[quoted text clipped - 39 lines]
> >
> > CCorreia

Reply to this Message

Allen Browne - 13 Mar 2007 01:02 GMT

Replies in-line with your questions.

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Hi Allen,
>
[quoted text clipped - 6 lines]
> 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
[quoted text clipped - 4 lines]
> 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
[quoted text clipped - 4 lines]
>
> 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
[quoted text clipped - 54 lines]
>> >
>> > CCorreia

Reply to this Message