Keyword Search

[PieOPah]

I am trying to write a query that will allow a partial word search.

I know that if I have something along the lines of

SELECT TblName.Name, TblName.Categor y, TblName.Type,
TblName.Ingredi ents, TblName.Instruc tions
FROM TblName
WHERE (((TblName.Name ) Like [Keyword Search]));

then this will allow the user to type in their keyword and have the
results bring back anything that is an exact match.

In SQL I would use like %red% to find anything that contained the
string red. How do I do a Like search of ths nature in Access, keeping
in mind that I still want the user to be able to type in their string.

I have tried a number of varients using % and * but nothing working....

Thanks in advance!

[inkman04]

Re: Keyword Search

Perhaps you can put some extra wild card characters
in your WHERE clause:

WHERE (((TblName.Name ) Like "*" & [Keyword Search] & "*"));

This should help you in the right direction.

Regards

[MGFoster]

Re: Keyword Search

Tried this:

WHERE [Name] Like "*" & [Keyword Search] & "*"

--
MGFoster:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)


PieOPah wrote:[color=blue]
> I am trying to write a query that will allow a partial word search.
>
> I know that if I have something along the lines of
>
> SELECT TblName.Name, TblName.Categor y, TblName.Type,
> TblName.Ingredi ents, TblName.Instruc tions
> FROM TblName
> WHERE (((TblName.Name ) Like [Keyword Search]));
>
> then this will allow the user to type in their keyword and have the
> results bring back anything that is an exact match.
>
> In SQL I would use like %red% to find anything that contained the
> string red. How do I do a Like search of ths nature in Access, keeping
> in mind that I still want the user to be able to type in their string.
>
> I have tried a number of varients using % and * but nothing working....
>
> Thanks in advance!
>[/color]

[Tim Marshall]

Re: Keyword Search

PieOPah wrote:
[color=blue]
> In SQL I would use like %red% to find anything that contained the
> string red. How do I do a Like search of ths nature in Access, keeping
> in mind that I still want the user to be able to type in their string.[/color]

What Inkman and MGFoster have suggested work and I have no variation on
that.

What I would suggest, however, is possibly an alternative way to the way
you are doing, ie, a means by which your results are changed as the user
types in a keyword. I use it quite frequently and successfully, but if
anyone else sees anything fundamentally wrong or dangerous with it,
hopefully they will speak up! 8)

For this, on your form you need two text boxes. Say the first is
UNBOUND and is called "txtSearchEnter " and is visible and indeed is the
text box in which a user types in a keyword. The second is also unbound
and we'll call it "txtSearch" and it's visible property is set to NO.

Set the rowsource of a list box or the record source for a subform the
same as what inpen or MGFoster wrote... in this case:

SELECT Name, Category, Type, Ingredients, Instructions
FROM TblName
WHERE Name Like "*" & [Forms]![frmWithAboveTex tBoxes].[txtSearch] & "*"

Note - I wouldn't use "Name" as a table field name - it's an Access
reserved word that could well cause you lots of grief.

In the on change event of txtSearchEnter, set up the following VBA - I
am using a list box called "lstResults ". lstResults has the above
described row source. The syntax will vary a little if you use a
subform instead:

Private Sub txtSearchEnter_ Change()

Me.txtSearch.Va lue = Me.txtSearchEnt er.Text

Me.lstResults.R equery

End Sub

It's important that the text property is used. What this does is use
whatever is typed in txtSearchEnter in the where clause.

As I said, I've used this a number of times successfully. I uaully
include a button that clears stuff, ie, with the event procedure like so:

Private Sub btnClear_Click( )

Me.txtSearchEnt er.Value = Null

Me.txtSearch.Va lue = Null

Me.lstResults.R equery

End Sub
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[PieOPah]

Re: Keyword Search

Thank you all for your help :) This certainly makes things much easier
for me :D

[PieOPah]

Re: Keyword Search

Thanks for the advise on t he field name :) I have adjusted this and
was pleasnatly supprised when all of my forms and queries automatically
updated themselves.

I was expecting a big job going through everything having to manually
change it :D

[Tim Marshall]

Re: Keyword Search

PieOPah wrote:[color=blue]
> Thanks for the advise on t he field name :) I have adjusted this and
> was pleasnatly supprised when all of my forms and queries automatically
> updated themselves.
>
> I was expecting a big job going through everything having to manually
> change it :D[/color]

I hate to be a wet blanket, but the reason the names changed was because
Access comes with Name Autocorrect on. I moved up from A97 late in the
game and took it as a best practice from the folks here to turn Name
Autocorrect Off - the common name for here is "Autocorrup t" an
apparantly it has some awful side effects at some point.

Allen Browne discusses this on his Access Tips web pages. See

http://allenbrowne.com/bug-03.html

What I have done to do the same sort of thing you've described is using
Rick Ficher's Find & Replace: http://www.rickworld.com/

You can try it for a little while for free and if, like I did, you find
it an indispensable tool, $35 USD is not much to cough up for it.

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "What's UP, Dittoooooo?" - Ditto