Please help me with query...I'm stupid :)

[pihutch]

I have a database in which one of the fields on my main table contains a narrative description of the whole record. I have a form that will display all records in the table. I have command buttons on the switchboard that will display that form with only the records that match specific criteria entered into text boxes by the user. I have no problem setting those up as long as there is only one thing stored in the field (i.e. date, last name, city, etc.). What I want to do is allow the user to enter a keyword or phrase into a text box and have the display form only show those records that contain that keyword or phrase in the description field. Any help anyone can provide is greatly appreciated!

Cheers,
Patrick

 

[M8KWR]

have a query look at the text box that you want the user to enter a single word, or phrase.

In the query, under each of the header you want to search in type something like

"*" & [forms]![FORMNAME]![TEXTBOXNAME] & "*"

Because you want this to search in any one of the 5 fields, do not put this in a line, stagger it down (if that makes sense, so you aren't saying and, but doing the or.

If you are unsure post you sql statement, and i will alter it for you.

 

[pihutch]

Thanks for the tip. I have discovered that I am not really doing a query. What I am doing is using the Access wizard to have the command button open the other form and display certain records based on what the user types in the text box. Here is the code that Access creates for the button's click event:

Private Sub Command75_Click()
On Error GoTo Err_Command75_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmModifyAdd"

stLinkCriteria = "[Activity=]"&"'"&Me![Text73]&"'"
DoCmd.OpenForm stDocName,,,stLinkCriteria

Exit_Command75_Click:
Exit Sub

When I change the single quotes in the expression to asterisks so that the expression looks like this:

stLinkCriteria = "[Activity=]"&"*"&Me![Text73]&"*"

...and I test the form by entering a keyword (test is the keyword) and clicking the command button, I get this error message:

Syntax error (missing operator) in query expression '[Activity]=*test*'

Do you have any idea what I am doing wrong here? If I leave the single quotes in the expression instead of the asterisks, it will not return any records because none of the fields contain ONLY the keyword entered. I need it to find all records that have the keyword anywhere in the [Activity] field.

Thanks again if you can help.

Cheers,
Patrick

 

[dhookom]

stLinkCriteria = "[Activity] Like '*" & Me![Text73]& "*'"

BTW: You should do yourself a favor and give your controls a meaningfull name. "Text73" looks a bit lax.


Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[pihutch]

Thank you so very much!!! That worked perfectly. I knew the asterisk needed to be in there somewhere, but just didn't know the exact syntax. I took VB and Access in college a couple of years ago, but don't remember much of it. As far as naming the controls goes, I was just trying to figure this problem out first.

Cheers,
Patrick