Is this hard to do???

[pihutch]

I asked a question a couple of days ago about a problem I was having with a form and now I have a new one. Here is the thread for the original question if anyone wants to review the original problem:

http://www.tek-tips.com/viewthread.cfm?qid=919138

dhookom was nice enough to help me figure out how to open a form from a command button and filter the records displayed in the form based on criteria specified by the user in a text box. As it stands right now, the records are displayed only if the syntax of the criteria entered by the user matches the exact context of some keyword or phrase in the table. How hard/complicated is it to modify the database to make the filter more flexible so that the user can enter multiple keywords in one text box? What I would like to do is make it work more like a search engine where the user can use operators like AND, OR and quotations. Right now, if a user enters "box AND cutter", the form will only display records that contain "box AND cutter" in that exact context, not records containing both "box" and "cutter" anywhere in the appropriate field. Is this beyond the abilities of most mere mortal Access users or is it something I might be able to accomplish with a reasonable amount of effort?

Cheers,
Patrick

 

[M8KWR]

you could ask the user to be more intelligent with what they enter into the text box.

For example, you have

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

if you change this to 

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

Get the user to enter things like *box* AND like *cutter*, and it should work

You could take this one stage further, and build a frontend query generator.

hope this helps.

 

[pihutch]

Thanks for the tip, but that didn't work. When I modify the variable assignment statement to match the example you gave, I get a syntax error when I submit the form. Any idea why? I noticed the last quotation mark is missing from the statement, but that didn't make a difference.

As far as a frontend query generator, is that something that would have to be built with outside of Access or is it something I could do with a form?

Thanks,
Patrick

 

[FoxProProgrammer]

Hey, Patrick. You are going to have to write Visual Basic code to build a Query string that contains "AND", "OR", and whatever other logical operators you want to allow. Conceptually, you could do this:

Two or more text boxes to enter the data to search for.
A pull down list box where the user would select the logical operator.
A button to do the query.

When the user presses the button, you would execute an OnClick routine that would build the query string and run the query. For example:

strQuery = "Select * From yourtbl Where somefld = " & txtbox1 & " " & listbox1 & " " & txtbox2

This example should give you a start, but obviously it won't work as is.

Regards,

dz
dzaccess@yahoo.com