Multiple word search criteria in a query

[ramez75]

The criteria will be
All words or part words in any one field. So the translation will be based on my database. The vba/query should search the "Description" field in tbltest for anything the USER types in txtSearch in frmtest.

e.g. if the USER types in "663,may" in the txtSearch field the out put should be records with ID 1,2,4,5. If they type in "66345,may be" then the output will be records 2 and 5

Can we be very clear on meet the criteria.

Any word or part word in any field

All words or part words in any one field

Any word or part word in any one field

 

[Brianwarnock]

This is how I would have done it to run a query, you I see want to write results to the search form, atleast I think you do.

Brian

 

[jdraw]

Isn't that what your database does now?

I'm still having connection issues.

 

[jdraw]

I just got Brian's mdb.

First test with 663,may
returns 1,2,4,5

second test 66345,may be
gives 1,2,5

Which is what I would expect if you match with like "*sword(i)*"

Ramez75, I'm not sure what you would use to search for "66345,may be" and get the output record IDs 2 and 5.

I see you created a value list, and if that gives you what you need, it seems fine.

 

[ramez75]

Thats exactly what I wanted. LEt me see how you did it and make sure I understand the logic

This is how I would have done it to run a query, you I see want to write results to the search form, atleast I think you do.

Brian

 

[ramez75]

Thank you all for the help, thats great. I will take what you did and use it in the live database.

So you used the split function to create an array with all the words in "txtSearch" then you fed the array into the Instr() function which looks into the description field and then uses the criteria in the query "found" since fsearch=found.

Thank you so much, I learned about the Instr() function definitely very useful

 

[jdraw]

Here's another version - good luck.

The code behind my search button is similar to the proc from the database I referenced in an earlier post.

 

[Brianwarnock]

Thank you all for the help, thats great. I will take what you did and use it in the live database.

So you used the split function to create an array with all the words in "txtSearch" then you fed the array into the Instr() function which looks into the description field and then uses the criteria in the query "found" since fsearch=found.

Not quite, the query is qrytest , "found" is returned by the function if a hit is found and this is tested for in the query's criteria to select the record, """" ie a zero length string is returned if no matches are found.

But maybe that's what you meant.

Brian

 

[ramez75]

Brianwarnock,

I transferred the fsearch function to the live database to be able to allow the USERS to have multiple word search. Well once I replace tbltest wit hthe actual table that I have in the live database I get this error

Run-time error '94' Invalid use of Null.

I look at the debug and it highlights

swords = Split(Forms!frmSearch4!txtSearch.Value, ",")

Well I use tbltest then alll works good.

Ofcourse the difference between tbltest and the live table (2002-noncomform) is 6300 records versus 6 and 34 fields versus 4

Any idea why or is there something I need to do.

Not quite, the query is qrytest , "found" is returned by the function if a hit is found and this is tested for in the query's criteria to select the record, """" ie a zero length string is returned if no matches are found.

But maybe that's what you meant.

Brian

 

[Brianwarnock]

Not off hand, it would suggest that there is a problem with the text string, but not having ever had the error I cannot suggest what, I would look for double , ie ,, but don't know if that would give the error.

I don't know if I can spend time on this this evening but if I get a chance will see if I can generate the error.

Brian

 

[ramez75]

Ok, so I did some digging and it sees when I use a partial keyword I get this error. If I use the exact keyword then the module works. On the other hand I dont see this issue in tbltest but again the live table has 6300 records some records might be causing this issue.

Not off hand, it would suggest that there is a problem with the text string, but not having ever had the error I cannot suggest what, I would look for double , ie ,, but don't know if that would give the error.

I don't know if I can spend time on this this evening but if I get a chance will see if I can generate the error.

Brian

 

[Brianwarnock]

I am totally bemused by what you are telling me but lets see if we can narrow things down,Change the function to

Function fsearch(description As Variant, ID) As String

and the query to

fsearch([Description,ID) with the criteria as "Found" just as before, now when the search stops we can mouse over the ID in the function and see what record we are on.

Brian

 

[ramez75]

Brianwarnock,

I tried what you suggested. The result was if I search for partial I get the error.

Example:

In the live table "Description" field had "6632456,Cap Broken", the ID was 6502. If I type in txtSearch "6632456" no issues I get the record everything works fine. On the other hand if I type "6632" I get the error I mentioned in the previous few posts. I hovered over the ID and I got 6502.
I went another step further, I deleted the record with ID 6502. I ran it again with a partial word in txtSearch and I get the same error.

So it seems from this experiment that when I use partial words the Module crashes at the split function but if I use the exact word then no issues.

Hope I am making sense

 

[Brianwarnock]

I understand what you are saying, but not what is happening.
I take it that 6502 is not the first record and that partial searches have worked for previous records?
Actually from your post "6632456" is a partial text as there are no spaces in
"6632456,Cap" , but I cannot see how this can effect the Split, its not dealing with the record. I wonder if this is a red herring and the problem was with the previous record.

I doubt if I am going to resolve this remotely if at all, but we can keep trying.

Brian

 

[ramez75]

I will modify the live database and keep a couple of records that the code is failing and post it maybe if you see the problem that will make it easier to troubleshoot

 

[Brianwarnock]

Ok but it will need to be in MDB format as I am pre 2007

Brian

 

[jdraw]

Just out of curiosity, did you try the sample database I posted?

I'm heading out for a few hours.

 

[ramez75]

I have attached the database. Try "6632" or "6632,6633" and you will get the error

 

[ramez75]

Yes it worked for the tbltest but never tried it for the live as I got distracted with this

I will give it a shot definitely

Just out of curiosity, did you try the sample database I posted?

I'm heading out for a few hours.

 

[Brianwarnock]

I've had a quick look and am totally flumoxed, it behaves differently when stepping through the code than when just running.

What happens is some records cause " an unkown Jet error"
When you click help on the message you get

There was a type mismatch when creating a table validation rule or a CHECK constraint on a column. (Error 3072)
For example, a table level validation rule that references a numeric data type must have a numeric validation clause. If a string validation clause is used, then this error will be returned.

As you continue control passes to the Form module and Txtsearch is cleared and the run stops.

When you allow the code to just run you end up trying to create an Array from a Null txtsearch which fails, how you get there I don't know.

The question is why the Jet error, and I have no idea.

Hopefully JDraw's method will work.

Brian