SQL search string

Cinnamon
03-06-2002, 12:38 PM
Ok... you knew I'd be back with another problem. Now that I'm in the testing phase. The error messages are working and ONE WORD searches are perfect. That brings me to a the problem.

I clicked the SEARCH command button and typed in "abandoned" and got the 10 records I expected. The ENTRY field of the records contains the text "abandoned iron mines of Chester Borough". However... when I click on the SEARCH command button and type in "abandoned mines" I get NO MATCHES. I was hoping to get the same 10 as before.

Scratching my head and looking confused.... what do I do to fix this?


Private Sub Command6_Click()
Dim sSQL As String
Dim INQUIRE As String
Dim Count As Integer

On Error GoTo ErrLabel:


INQUIRE = InputBox("Enter search criteria (leave blank for all):")

sSQL = "SELECT * FROM cards WHERE entry LIKE '%" & INQUIRE & "%'"
Set rsDB = New ADODB.Recordset

If INQUIRE = "" Then
rsDB.Open "SELECT * FROM Cards", cnDB, adOpenKeyset, adLockOptimistic, adCmdText
Else
rsDB.Open sSQL, cnDB, adOpenKeyset, adLockOptimistic, adCmdText
End If

Set MSHFG1.DataSource = rsDB

Count = rsDB.RecordCount

rsDB.MoveFirst
Image1.Picture = LoadPicture(rsDB.Fields("CardPath"))
Form1.Text1.Text = rsDB.Fields("Unique#")
Form1.Text2.Text = rsDB.Fields("CardNum")
Form1.Text3.Text = rsDB.Fields("CardPath")
Form1.Text4.Text = rsDB.Fields("Entry") & ""
Form1.Text5.Text = Count

Exit Sub

ErrLabel:
MsgBox ("There are no matches to your inquiry.")

End Sub


Cinnamon

Ales Zigon
03-06-2002, 01:04 PM
You should split your wildcard criteria in two.

Something like this:

'go for the original query
If RecordSet .EOF Then 'found no match
'set new criteria
SQL="SELECT...... FROM Cards WHERE Entry LIKE '%abandoned%' OR Entry LIKE '%mines%';"
Requery

usetheforce2
03-06-2002, 01:05 PM
ah, took me a bit to see it

"abandoned mines" <-- is the string you're searching for which doesn't exit in the:
"abandoned iron mines of Chester Borough" <-- the string you are searching.

you may have to parse the string (abandoned mines) to perform you search


regan

Cinnamon
03-06-2002, 01:12 PM
Ok.... I'm off digging in my VB manuals to learn how to parse a string!

Cinnamon

Thinker
03-06-2002, 01:15 PM
zigona almost has it, but I would change Or to And

SQL="SELECT...... FROM Cards WHERE Entry LIKE '%abandoned%' And Entry LIKE '%mines%';"

usetheforce2
03-06-2002, 01:15 PM
hey,

a simple way is to use the Slit function.

dim parse() as string

searchstring = "abandoned mines"

parse = split(SearchString, " ")

output:

parse(0) <-- would be "abandoned"
parse(1) <-- would be "mines"

regan

reboot
03-06-2002, 01:21 PM
Just guessing here but, is the Slit function similar to the Split function? :p

usetheforce2
03-06-2002, 01:25 PM
LOL

:eek:

Thanks reboot :)

Cinnamon
03-06-2002, 01:26 PM
Correct me if I'm wrong....
but my search string is a variable called INQUIRE.

this variable could contain "abandoned mines" or "abandoned chester" or "iron borough" or even "abandoned mines borough"

Would'nt I need to break (parse) these out and then let the search check for any instance of each of them.

Just a thought... I could be way off. Please let me know.

Cinnamon

usetheforce2
03-06-2002, 01:35 PM
hey,


thats no prob if you where looking for:

if INQUIER = "abandoned mines"

"abandoned mines in sudbury"

it would work. But you are searching

"abandoned iron mines of Chester Borough" <-- you would have to search for "abandoned iron minds"

for every additional word you will have to add a Like field %value%

thinkers:

SQL="SELECT...... FROM Cards WHERE Entry LIKE '%abandoned%' And Entry LIKE '%mines%';"



will find any record that contains both word (abandoned, mines)

if you wanted to search for three words you'd need:

SQL="SELECT...... FROM Cards WHERE Entry LIKE '%abandoned%' And Entry LIKE '%mines% And Entry LIKE '%borough%';"


hope that makes sense:)

Regan

Ales Zigon
03-06-2002, 03:24 PM
Just to wrap this up. You can use this procedure to parse a string into the SQL statement
Dim INQUIRE As String, AddInquire As String, Parse() As String, iCount As Integer
INQUIRE = "SELECT * FROM Cards WHERE Entry LIKE '%"
If InStr(1, Text1.Text, Chr$(32)) <> 0 Then
Parse = Split(Text1.Text, Chr$(32))
For iCount = 0 To UBound(Parse)
If iCount < UBound(Parse) Then
AddInquire = AddInquire & Parse(iCount) & "%' And Entry LIKE '%"
Else
AddInquire = AddInquire & Parse(iCount)
End If
Next iCount
INQUIRE = INQUIRE & AddInquire & "%';"
Else
INQUIRE = INQUIRE & Text1.Text & "%';"
End If

mhsueh001
03-06-2002, 03:33 PM
I broght this up in another thread, but didn't get a response.
I had code similar to what zigona has.

What happens when AddInquire exceeds a 255 character string length?

If I'm doing a long search and you build your where string to a point where it exceeds 255 characters it will generate a syntax error.

I ended up using multiple arrays of strings which was not pretty.

Are arrays of strings the only way to get around the 255 character string limitation?

Cinnamon
03-06-2002, 03:37 PM
I'll give all this a try first thing tomorrow morning!

Cinnamon

Ales Zigon
03-06-2002, 03:58 PM
Originally posted by mhsueh001
What happens when AddInquire exceeds a 255 character string length?

If I'm doing a long search and you build your where string to a point where it exceeds 255 characters it will generate a syntax error.



Nothing. There will be no error.
You can have string as long as 2 billion characters [2^31] (var lenght string).

Cinnamon
03-07-2002, 09:46 AM
Zigona the code works great. I modified it a bit to allow user input . Thanks again!

Cinnamon

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum