C
CompleteNewb
I'm trying to make it so that a query will search 2 (TWO) fields for ANY of
the words entered in an unbound text box on a form. The words would be
separated by spaces.
So, if someone enters "Who Where" in the text box, the query will search
Field1 for "Who" and/or "Where", and it will ALSO search Field2 for "Who"
and/or "Where"
Below are examples and some over-explanation (a lot of the posts I see have
responses that say the asker did not include enough information, so I'm
trying to explain as completely as possible).
Example (I did not enclose things in quotes, because I didn't want anyone
thinking quotes are part of the field values):
In TableX:
Record1:
Field1: Computer, Axes, Candy
Field2: Jibbles, Tanks, PC
Record2:
Field1: Tanks, Cars, Puzzles
Field2: Who likes candy
- In the text box named Searcher on Form1, user enters Likes Axes. Records
returned are:
Record1 (the word Axes is in Field1)
Record2 (the word Likes is in Field2)
- User enters Brunch Candy. Records returned are:
Record1 (the word Candy is in Field1)
Record2 (the word Candy is in Field2)
- User enters Likes Tubby. Records returned are:
Record2 (the word Likes is in Field2)
On the fantastic newsgroups I found some code that did EXACTLY what I'm
trying to do, except it's a procedure to set filter properties of a form,
and only filters on one field. I want to search two fields, and I want it
to be a query as the recordsource instead of a filter (I will be converting
the whole kit n' kaboodle to SQL at some point, but I'm using Access as a
testing environment first. I know filters can be viewed as SQL at some
point, but I'm trying to handle this with the recordsource being a query
that I can study and modify/reverse engineer, etc.).
The closest I have been able to come is a query that uses the whole "Like *
& [forms]![SearchForm]![Searcher] & *" (obviously not the entire code, since
I'm trying to avoid an overabundance of quotes in this post) as the criteria
in 2 fields in the query builder (on 2 separate lines, so it's an "OR"
between the two different fields), but this does not parse separate words
(separated by a space) in the Searcher textbox and search the 2 fields for
ANY of the words; instead, it looks for the exact text of [Searcher] located
anywhere in either of the two fields, so it does not look for "Eye" and also
"Candy" in EITHER of the two fields when the user enters "Eye Candy."
Instead, it looks for the entire phrase "Eye Candy" somewhere in either of
the two fields. This bad. Searching for "Eye" in Field1, searching for
"Candy" in Field1, searching for "Eye" in Field2, searching for "Candy" in
Field2; this good.
I found some VBA that parses words separated by spaces in the Searcher text
box, but I can't figure out how to incorporate the parsing code into the
query/SQL statement as the criteria for 2 fields. Every time I think I get
close, I get VBA errors or wrong records or no records.
Any help would be greatly appreciated. If someone could supply the SQL
statement assuming fieldnames given above, that would be awesome, since I
could then check out the Query Builder structure in the Access query builder
and get a better understanding of how to use expressions, etc. as part of my
query building (something I'm woefully ignorant of).
Thank you very much for any help, and for reading. It's places like this
that give me hope for the Internet (as opposed to a porn repository :>))
Complete Newb
PS: ...not that there's anything wrong with porn... :>)
the words entered in an unbound text box on a form. The words would be
separated by spaces.
So, if someone enters "Who Where" in the text box, the query will search
Field1 for "Who" and/or "Where", and it will ALSO search Field2 for "Who"
and/or "Where"
Below are examples and some over-explanation (a lot of the posts I see have
responses that say the asker did not include enough information, so I'm
trying to explain as completely as possible).
Example (I did not enclose things in quotes, because I didn't want anyone
thinking quotes are part of the field values):
In TableX:
Record1:
Field1: Computer, Axes, Candy
Field2: Jibbles, Tanks, PC
Record2:
Field1: Tanks, Cars, Puzzles
Field2: Who likes candy
- In the text box named Searcher on Form1, user enters Likes Axes. Records
returned are:
Record1 (the word Axes is in Field1)
Record2 (the word Likes is in Field2)
- User enters Brunch Candy. Records returned are:
Record1 (the word Candy is in Field1)
Record2 (the word Candy is in Field2)
- User enters Likes Tubby. Records returned are:
Record2 (the word Likes is in Field2)
On the fantastic newsgroups I found some code that did EXACTLY what I'm
trying to do, except it's a procedure to set filter properties of a form,
and only filters on one field. I want to search two fields, and I want it
to be a query as the recordsource instead of a filter (I will be converting
the whole kit n' kaboodle to SQL at some point, but I'm using Access as a
testing environment first. I know filters can be viewed as SQL at some
point, but I'm trying to handle this with the recordsource being a query
that I can study and modify/reverse engineer, etc.).
The closest I have been able to come is a query that uses the whole "Like *
& [forms]![SearchForm]![Searcher] & *" (obviously not the entire code, since
I'm trying to avoid an overabundance of quotes in this post) as the criteria
in 2 fields in the query builder (on 2 separate lines, so it's an "OR"
between the two different fields), but this does not parse separate words
(separated by a space) in the Searcher textbox and search the 2 fields for
ANY of the words; instead, it looks for the exact text of [Searcher] located
anywhere in either of the two fields, so it does not look for "Eye" and also
"Candy" in EITHER of the two fields when the user enters "Eye Candy."
Instead, it looks for the entire phrase "Eye Candy" somewhere in either of
the two fields. This bad. Searching for "Eye" in Field1, searching for
"Candy" in Field1, searching for "Eye" in Field2, searching for "Candy" in
Field2; this good.
I found some VBA that parses words separated by spaces in the Searcher text
box, but I can't figure out how to incorporate the parsing code into the
query/SQL statement as the criteria for 2 fields. Every time I think I get
close, I get VBA errors or wrong records or no records.
Any help would be greatly appreciated. If someone could supply the SQL
statement assuming fieldnames given above, that would be awesome, since I
could then check out the Query Builder structure in the Access query builder
and get a better understanding of how to use expressions, etc. as part of my
query building (something I'm woefully ignorant of).
Thank you very much for any help, and for reading. It's places like this
that give me hope for the Internet (as opposed to a porn repository :>))
Complete Newb
PS: ...not that there's anything wrong with porn... :>)