Text Box on a Form as Criteria for a Query 2

[teluser]

I have a form that users need to be able to enter a text string or single word in a text box, then click a command button and it search for that text, which is usually somewhere in the middle of a line of text that is not uniform, in a particular field of a table. Is this possible without creating the whole query in code? I'm looking for something that would essentially work as if you typed *text* in the criteria of a field in a query.

 

[JoJ]

Hi teluser,

Basically, the solution you're looking for revolves around the InStr function.

If you explain how you plan to display the results of the text search, then maybe I can suggest ways to deliver a complete solution.

Is your text search restricted to a field in the current record? Or do you want to search within the field of the entire table and display the results in a subform?

 

[WildHare]

Or you could try using the LIKE function in a query, something like this:

=LIKE("*" & MyForm.TextBox & "*&quot

this would return any textguy with the characters your user entered, anywhere in the values of textguy

Jim Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[teluser]

Thanks for the quick responses,you folks are a lifesaver.

Here's my whole plan so you can get the big picture. Maybe my whole idea is wrong. I have one main form with buttons for various reports. Then I want to have that single text box for user input,(has to be able to be found anywhere in the field) then a list box that becomes visible after data is plugged into the text box and enter is pressed. I know this needs to be passed to the query in something like I use for my Combo Boxes.
[Forms]![frm_reports]![CboErr].[value]
The problem is the text entered in the text box is never going to be exact and could be found in the beginning, middle, or end of the field.


WildHare, I tried =LIKE("*" & MyForm.TextBox & "*&quot
and keep getting an invald syntax when I plug it in my query.

 

[JeremyNYC]

No = with LIKE =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.

 

[JoJ]

See if you can use this to start with:

1) text box name is txtSearch
2) list box name is List2
3) code the following in the after update of your text box (replace "table" & "field" with your table name and field name):

Private Sub txtSearch_AfterUpdate()

Dim strSQl As String
strSQl = "SELECT table.field" & vbCrLf
strSQl = strSQl & " FROM table" & vbCrLf
strSQl = strSQl & &quot; WHERE (((InStr([field],&quot; & Chr(34) & Me!txtSearch & Chr(34) & &quot)<>0));&quot;

Me!List2.RowSourceType = &quot;Table/Query&quot;
Me!List2.RowSource = strSQl
Me!List2.Requery
Me!List2.Visible = True
Me!List2 = Me!List2.ItemData(0)

End Sub

 

[teluser]

Thanks JoJ...Works great with a few minor changes to fit my needs. You have just gotten rid of a headache thats been bothering me for a while now. Below is what I did. I couldn't get it to work using a table so I made a query.
Again thanks a million!!!

Private Sub txtSearch_AfterUpdate()
Dim strSQl As String
strSQl = &quot;SELECT qry_search.pon, qry_search.cc, qry_search.ver, qry_search.tos, qry_search.reqtyp, qry_search.act, qry_search.terr_err_num, qry_search.error_description&quot; & vbCrLf
strSQl = strSQl & &quot; FROM qry_search&quot; & vbCrLf
strSQl = strSQl & &quot; WHERE (((InStr([error_description],&quot; & Chr(34) & Me!txtSearch & Chr(34) & &quot)<>0));&quot;

Me!List2.RowSourceType = &quot;Table/Query&quot;
Me!List2.RowSource = strSQl
Me!List2.Requery
Me!List2.Visible = True
Me!List2 = Me!List2.ItemData(0)
End Sub