SQL search string

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

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

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

Ok.... I'm off digging in my VB manuals to learn how to parse a string!

Cinnamon

zigona almost has it, but I would change Or to And

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

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

Just guessing here but, is the Slit function similar to the Split function? :p

LOL

:eek:

Thanks reboot :)

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

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

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

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?

I'll give all this a try first thing tomorrow morning!

Cinnamon

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).

Zigona the code works great. I modified it a bit to allow user input . Thanks again!

Cinnamon