finding the record

[ishikha]

hi friends,

i am having one problem.i have to make a find button such that it search the database is having the searched record with the similar name or there is some diff in name.

i.e i mean to say that
if the user wants to search for ZIPDRIVE or ZIPDRIVE or zipdrive
but in the database it exists as "zip drive" then it will be able to search it.

it will search the record with similar or if there is some difference in the searched string

plzzzzz tell me some sol. I tried a lot but unable to find it.
i am using adodb

ishikha

 

[sunaj]

Hmmm. It's not straight forward. I don't know of any good solution, maybe some of the experts will find a better solution for, but until then, here is my input.

There is no (as a I know of) way in Jet SQL to trip a string of spaces. It is therefore not possible to find 'zip drive' by searching on 'zipdrive'. The other way around is easy :
-----------------------------------------------------
s = "Zip Drive"
Rst.open " SELECT ... WHERE MyField LIKE '%" & s & "%' AND MyField LIKE '%" & s & "%'",Conn
-----------------------------------------------------

The only solution to your search that I can think of is to open a recordset with all the record and loop through it to find what you are looking for:
-----------------------------------------------------
s = "ZipDrive"
Rst.open " SELECT MyField FROM MyTable",Conn
If not Rst.eof and not Rst.Bof then
Rst.movefirst
While not Rst.eof
if InStr(1,replace(Rst.fields("MyField&quot.Value," ","&quot,replace(s," ","&quot)>0 then msgbox "Found one!"
Rst.movenext
Wend
else
'no fields in table
end if
-----------------------------------------------------
[pibe]



Sunaj
'The gap between theory and practice is not as wide in theory as it is in practice'

 

[sunaj]

Arrhg, Just a little too fast on the button. The first piece of code should be:
--------------------------------------------------------
s = "Zip Drive"
Rst.open " SELECT ... WHERE MyField LIKE '%" & replace(s," ","&quot & "%'",Conn
--------------------------------------------------------

Another possiblility is to search on the individual words:
--------------------------------------------------------
s = "Zip Drive"
a = split(s)
SQL = "MyField LIKE '%" & a(0) & "%'"
for i = 1 to ubound(a)
SQL = SQL & " AND MyField LIKE '%" & a(i) & "%'"
next i
Rst.open "SELECT ... WHERE " & SQL
--------------------------------------------------------


Sunaj
'The gap between theory and practice is not as wide in theory as it is in practice'

 

[strongm]

Using adodb against which backand DB? If it is something like SQL Server or Oracle then you could consider the built-in Soundex function. Sadly Jet SQL does not include the function (although you can always write your own...)

 

[ishikha]

hi sunaj,

i use the query

ans = InputBox("Enter the name of the object", "FIND&quot
strtable1 = "SELECT name FROM mmiscellaneous1 WHERE name LIKE '%" & Left(ans, 3) & "%'"
adoconn.Execute (strtable1)


in this query i am matching the first three words of the string
now i want to know that if the first three words matches with some records then how i am able come to know whether it founds the string or not.

and secondly if it founds the string then how i am able to retrieve the values of rest of the fields on the form

ishikha

 

[sunaj]

Hi ishikha,
Mike Strong has , as usually, a good point. I assumed that you are using an access database and therefore Jet SQL. Is that so?

To retrieve info form a database you must open a recordset. Something like...

------------------------------------------------------
ans = InputBox("Enter the name of the object", "FIND&quot
strtable1 = "SELECT name FROM mmiscellaneous1 WHERE name LIKE '%" & Left(ans, 3) & "%'"

Rst.open strtable1, adoconn
If not Rst.eof and not Rst.Bof then
Rst.movefirst
While not Rst.eof
'looping through the records, code here...
Rst.movenext
Wend
else
'no fields in table
end if
Rst.close
set Rst = nothing
------------------------------------------------------

Sunaj
'The gap between theory and practice is not as wide in theory as it is in practice'