Multiple word search criteria in a query

[jdraw]

I tried to run the test.mdb that was in the zip file test.zip in post#38.

I found there was no If IsNull check on the value to be Split before doing the For Next.
I added a default value for fsearch ("XXX_NotFound_XXX") on entry to the function.

The if to check the textbox value was this sort of format

If IsNull( the search field.value) Then
 exit function
else
  for...
  next
end if

I had difficulties with invalid use of Null. And then froze my machine.
Had to kill the MSAccess.exe to get control.

I'm on Vista home with all updates applied.
Access2010 with default to 2000-2003 mdb format.

jdraw

 

[Brianwarnock]

The check for value in the txtsearch field is made in the code behind the search button before the query is run. After the query is run the field is cleared, you can avoid the invalid null by omitting this, but would the result be correct?

It is not the invalid null that is the problem, that is the result of the problem which is the error I referred to above.

I showed the approach Using a query as the OP was having trouble with the full code method, which has the advantage of only building the array once, but did display the results on the form, which he may or may not have wanted.

Could you not have grafted your approach onto his DB to see if it gave the correct results?

I doubt that I will have time to look at this in detail until Monday

Brian

 

[jdraw]

Brian,

Could you not have grafted your approach onto his DB to see if it gave the correct results?

Yes, I could. But I gave him a sample database and created a table using his few test records. He could easily use his larger table to test.

He doesn't need me to test the data he has at his end.

He said it worked on the test set, but he hasn't tested with his real data yet. I don't think he is waiting on me.

But if he is and needs help, I'll pitch in.

 

[Brianwarnock]

Fair enough.

 

[Brianwarnock]

Ramez

I have changed a couple of things and it now works.

I have declared swords as Public
I have moved the creation of the arrayof rom the function to the srchcommand code immediately after the null test.

I checked that the data selected was correct by creating a simple query using multiple criteria of Like.... OR Like...

An added benefit the performance is noticeably improved.

Brian

 

[jdraw]

Brian,
Glad you sorted out the issue and have improved performance.

 

[tharukan]

I have read all of the thread. I do have a question. How can I show the filtered data back in a form?
Thank you.

 

[jdraw]

tharukan,
You have posted in a 7+ yr old thread. I recommend you start a new thread and describe your issue in plain, simple English. A mock up example of what you start with; the process to be done to get the output and a mock up of the desired output will simplify the communications involved.
Good luck.