(URGENT) Simple search - match any word / match all words ?

04-16-2004, 05:59 PM
Can anyone help me alter my code so that it searches for:

-Match any word

-Match all words

At the moment my code is only matching either the first word or exact strings, so its pretty poor.

My code so far is:

newMatchRS.Open "SELECT * From Task WHERE UCase(TaskInformation)
LIKE '%" & UCase(txtSearchCriteria) & "%' Order By TaskID", db,
adOpenStatic, adLockOptimistic

I havent got a lot of time for any messing with getting code to work, so anyone that has any quick help tips would be very appreciated

04-18-2004, 08:28 PM
You don't have time to get your code to work, but we're supposed to?

04-19-2004, 07:18 AM
You have posted several threads already with this same topic. We have provided various advice. Please stop cross posting..it will not get your questions answered any quicker. Review our advice and try to implement the strategies.

04-19-2004, 09:44 AM
I am simply asking for a suggestion as to what I need to change



I have tried several things such as and they're not working.

04-19-2004, 05:01 PM
what database are you using?

04-19-2004, 06:01 PM

04-19-2004, 06:14 PM
right well I'll re-post my solution for you which was so kindly deleted by one of our forum leaders whose nick I won't mention. any questions just ask:

newMatchRS.Open "SELECT * From Task WHERE Instr(UCase(TaskInformation),
UCase(" & chr(34) & txtSearchCriteria & chr(34) & ")) > 0 Order By TaskID", db, adOpenStatic, adLockOptimistic

04-20-2004, 08:05 PM
couch612 it looked very promising and i was almost kicking myself for not getting it, but its not quite right yet...

it still only matches either all words or strings of words...

i really need it to match ANY word if possible...

what its doing is - if i have a search string.... "VBKid loves VB" and a record which has the field "VBKid loves VB"...

and i search for that - it will return the record "VBKid loves VB" - ok

if i search for VBKid loves, or loves VB it will return the same record - ok

but if i search for "VBkid VB" - i am wanting it to return that record or any record where it matches ANY of the words, but its returning nothing at all...
(it should at least return that record VBKid loves VB,
as VBKid and VB are both in that string)...

any other ideas?

04-20-2004, 08:12 PM
ah well now this requires additional coding. what i would do is take your search string and split it into distinct words. then i would perform the search once for each distinct word in your search string.

to split the search string into distinct words, Access 2000 provides a handy function named Split, that takes a string and creates an array of the distinct words:

dim strArray() as string

'split the search criteria into a string array. criteria is delimited with spaces
strArray = Split(txtSearchCriteria," ")

then using the newly created array, do the search for each element in the array:

dim i as integer

for i = 0 to ubound(strArray)

'put search code here

next i

or alternatively, you could use the words in the array to generate the WHERE clause in your SQL statement and only have to perform the search once.

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum