Using wildcards (1 Viewer)

[jeanina]

I have a form with a subform linked by the MembersID. What I want to do is search the members table by using an input box. I've set up a query and put:

Like [Forms]![Membership]![MemberID]

in the criteria line. This gives me the correct form information and I've coded the subform to change with the form. However, I need to be able to have this query return matches using a wildcard before and after the entered string. I've tried every way I can think of (*[...]*, [*Text*], *([...])*)but have had no luck.

Any ideas would be appreciated.

 

[mikec]

The vertical pipe, "|", is your answer. Try using:
Like "*|[Forms]![Membership]![MemberID]|*"
in your query criteria.

 

[Caranath]

Use of vertical Pipe in Access 2002

The vertical pipe, "|", is your answer. Try using:
Like "*|[Forms]![Membership]![MemberID]|*"
in your query criteria.

Hi,

This does nor work anymore in Acces 2002. Does anyone know how to solve this problem in Acces 2002?

 

[a.sinatra]

Private Sub txtSearch_Change()
On Error GoTo Err_txtSearch_Change
    
    Me!txtsearchFind = "*" & Me!txtSearch.Text & "*"
    Me.txtsearchList.Requery
    
Exit_txtSearch_Change:
    Exit Sub

Err_txtSearch_Change:
    MsgBox Err.Description, , ErrTitle
    Resume Exit_txtSearch_Change

End Sub

You are going to need 2 Fields (one hidden and one not)

The "txtSearch" is the box your going to type into, "txtSearchFind" will be hidden and will be the field linked to the query. "txtSearchFind" needs a default of "*"

Hope that helps!
________
MAZDA CX-5 HISTORY

 

[Caranath]

Thank You,

The problem is solved

Gerard

 

[MICHELE]

Hi! I was looking at this post because it's exactly what I need.
However, it's not quite working the way it should. I set up the 2 fields on my form: txtSearch and txtSearchFind, but when I try to open my query using the field txtSearchFind as my criteria, it pulls up nothing. I can see that the field (txtSearchFind) has a *3* in there, but it is not using that as criteria.

Also, what exactly do I need to requery and why? Maybe that's my problem. Thank you!

 

[Mile-O]

    Me!txtsearchFind = "*" & Me!txtSearch.Text & "*"
    Me.txtsearchList.Requery

    Me.txtsearchFind Like "*" & Me.txtSearch & "*"
    Me.txtsearchList.Requery

Three changes in those two lines.

• By saying = "*" & Me!txtSearch.Text & "*" you are asking for a literal equals to. So it txtSearch contained the word "frog" then you woule literally be looking for an exact match of "*frog*";
• I dropped the .Text property as that's the default property of a textbox and, if no property is entered, .Text is assumed. There's nothing wrong with disambiguating though;
• Change the Me! to Me. - the code was inconsistent. Line 1 used Me! and then line 2 contained Me. - always, if possible, use the dot syntax over the other.

 

[a.sinatra]

But because of the two asteriks, it searches everything, so i wouldnt be getting any different results because my query is set up like this.. "Like [txtSearchFind]" correct?
________
Porn

 

[Mile-O]

But because of the two asteriks, it searches everything, so i wouldnt be getting any different results because my query is set up like this.. "Like [txtSearchFind]" correct?

I was drawing attention to the = with the wildcards as they would have been interpreted literally. Like, however, interprets wildcards (whether it be ? or *) as missing characters.

 

[MICHELE]

Thank you very much! I changed all my !'s to .'s and removed the .Text. It seems to be copying txtSearch to txtSearchFind okay, but my queary just seems to ignore what's in txtSearchFind. Here is the criteria I'm using. Pretty straighforward, but maybe there's something wrong. I even deleted all the other criteria in my query to see if it would work.

[Forms]![F_SEARCH SCREEN]![txtSearchFind]

Also, here is the code that is in my form. I changed it a little, so maybe you can see something wrong in it. I don't have a txtSearchList field, so I wasn't sure what to requery.

Private Sub txtSearch_AfterUpdate()

On Error GoTo Err_Event

Me.txtSearchFind = "*" & Me.txtSearch & "*"
Me.txtSearchFind.Requery
Me.txtSearchFind.SetFocus


Exit_Event:
Exit Sub

Err_Event:
MsgBox Err.DESCRIPTION
Resume Exit_Event


End Sub



Thank you for any help! I've looked at this for so long, I"m probably looking over something very obvious.

 

[Mile-O]

Delete these lines:

Me.txtSearchFind.Requery
Me.txtSearchFind.SetFocus

You don't need them.