Using wildcard character to search a field 2

[anniesolomon]

I'm still having a lot of trouble inserting SQL statements in VBA. Here's what I have:

Me.Filter = "[Word]='" & Me![SearchForThisWord] & "'"

The problem is I don't want the user to have to search for the Exact word. If I were building a query, I'd use *.* but I don't know how to get that into the beginning and end of my statement.

 

[strongm]

You may want to consider the key word 'Like' rather than '='

 

[TheAceMan1]

How are ya anniesolomon . . .

Perform a google for [blue]SQL Wild Card Operators[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[anniesolomon]

Thanks but using the word Like alone doesn't help. I would use it with an asterisk wild card before and after the value in the text field but I can't figure out the correct placement of the quotation marks. That's what get me every time!

 

[strongm]

You might want to have a look at thread705-1670611 where we recently covered something similar

 

[Andrzejek]

Try:
[tt]
Me.Filter = "[Word] LIKE '*" & Me![SearchForThisWord] & "*'"
[/tt]
Or better yet:

[tt]
strFilter = "[Word] LIKE '*" & Me![SearchForThisWord] & "*'"
Debug.Print strFilter
Me.Filter = strFilter
[/tt]


Have fun.

---- Andy

 

[TheAceMan1]

anniesolomon . . .

[blue]strongm[/blue] was just opening the door to the [blue]Like[/blue] operator which is typically used with [blue]wild cards![/blue] There are many outcomes depending on how you use the wild cards ... [purple]I'm assuming your familiar with these wild cards.[/purple]

A basic search for you (depending on whats entered in [blue]SearchForThisWord[/blue]) would be:

[blue] Me.Filter = "[Word] LIKE '*" & Me![SearchForThisWord] & "*'"[/blue]

This would return all records where [blue][Word][/blue] contains [blue]SearchForThisWord[/blue].

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[anniesolomon]

Hi AceMan. Yes, I'm familiar with the wild cards. It's those darned quotation marks that get me every time. I just cannot seem to grasp how to properly place them and when to use single versus double quotes. I really appreciate your help. Of course, your code works perfectly! Please enjoy a star from me.