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

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
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"CCorreia" <CCorreia@discussions.microsoft.com> wrote in message
news:8979B621-229C-4E28-B54A-4AC11E920BE0@microsoft.com...
> 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 

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

"Allen Browne" wrote:

> 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
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
> 
> "CCorreia" <CCorreia@discussions.microsoft.com> wrote in message
> news:8979B621-229C-4E28-B54A-4AC11E920BE0@microsoft.com...
> > 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 
> 
> 

Replies in-line with your questions.

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

"CCorreia" <CCorreia@discussions.microsoft.com> wrote in message
news:3B603E50-E08D-45DF-A3B4-35348FEDA060@microsoft.com...
> 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
>
> "Allen Browne" wrote:
>
>> 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.
>>
>> "CCorreia" <CCorreia@discussions.microsoft.com> wrote in message
>> news:8979B621-229C-4E28-B54A-4AC11E920BE0@microsoft.com...
>> > 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