deciding on how many words are entered

[pinkcat]

Hi

I was wondering how would can I know how many words are entered in textbox and how to split the text entered into the textbox in order to use them in SQL query as with or, and or like?

[nkad]

Dim sqlsearch() as string

sqlsearch = Split(text1.text, chr(32), 1)

will take text in text one and split it into words and put it in the array sqlsearch. Fromt there you can do

wordcount = ubound(sqlsearch) + 1

to find the word count

[pinkcat]

And how can I use it with my following code so that if an option is selected which requires all the words entered in a text box to be joined by and or search it as a phrase or search tem by joining with or. All the options is in the combobox?

Thanks

Private Sub cmdSearch1_Click()
Dim sTarget As String
Dim wordcount As Integer
Dim i As Integer
moRS.MoveFirst
sTarget = Replace$(txtSearch1.Text, "*", "%")
sTarget = Replace$(sTarget, "'", "''")
Dim sqlsearch() As String
sqlsearch = Split(sTarget, Chr(32), 1)
wordcount = UBound(sqlsearch) + 1
 
 
'Decide on right SQL statement
If optKeyword.Value = True Then
moRS.Find "Keywords like '" & sqlsearch & "'"
ElseIf optTitle.Value = True Then
moRS.Find "Article_Name like '" & sqlsearch & "'"
ElseIf optAuthor.Value = True Then
moRS.Find "Author like '" & sqlsearch & "'"
ElseIf optJournaltitle.Value = True Then
moRS.Find "Journal_Name like '" & sqlsearch & "'"
End If
 
 
If moRS.EOF Then
MsgBox "no match found", vbOKOnly, "sorry"
Else
MsgBox "found " & moRS.Fields("Journal_Name").Value
End If
End Sub

[nkad]

Edit your original code so that it can generate both types of sql statements. Check if the option is selected with an IF statement. I have code i wrote for this, but, you sould write it. Its good experiance!

[jhermiz]

Sounds like you are looking for:

[MartinLiss]

Is that something you wrote?

[jhermiz]

Originally posted by MartinLiss
Is that something you wrote?

Yes why do you ask ?

[jhermiz]

Originally posted by MartinLiss
Is that something you wrote?

T'was quite painful

[nkad]

Where did you get the sweet icons? I suck at graphics.

[jhermiz]

Originally posted by nkad
Where did you get the sweet icons? I suck at graphics.

If you look real close at the icons you'll see I modified them..look into snagit (www.camtasia.com)...then zoom in...drop pellets of paint, paintbrush works too, adobe is better....always remember not to use solids :-). Majority our from various apps we have here with a touch of changes to fit my needs.

Jon

[IGBP]

Jhermiz ...
Nice work

[pinkcat]

So far I have managed to split the entered string into words and I managed to use the options selected in the combobox and optionlist but I still couln't figure out how I can construct the SQL statement that joins all the splitted words by or or and can u pls show me an example SQL statement where it searchs all the words in an array considering the number of words in the array?


Private Sub cmdSearch1_Click()
Dim RS As Recordset
Dim sqlsearch() As String
Dim retString() As String
Dim strSplit As String
Dim i As Long
Dim SQL As String

If Len(Trim(txtInput.Text)) <> 0 Then
lstCities.Clear
strSplit = txtInput.Text
retString = Split(strSplit, Chr(32))

'****
'For i = LBound(retString) To UBound(retString)

'If optKeyword.Value = True And cboChoice.Text = "[find any word]" Then

'SQL = ???

'***

Set RS = DB.OpenRecordset(SQL)

[MartinLiss]

Try this although I haven't tested it. BTW is this DAO?

Private Sub cmdSearch1_Click()
Dim RS As Recordset
Dim sqlsearch() As String
Dim retString() As String
Dim strSplit As String
Dim i As Long
Dim SQL As String
 
If LenB(Trim(txtInput.Text)) <> 0 Then
    'lstCities.Clear
    SQL = "Select * From MyTable Where "
    retString = Split(txtInput.Text, " ")
    If optKeyword.Value = True And cboChoice.Text = "[find any word]" Then
        For i = 0 To UBound(retString) ' LBound is always 0 from Split
            SQL = SQL & "MyField = " & retString(i) & " or "
        Next
        ' Remove the trailing " or "
        SQL = Left$(SQL, Len(SQL) - 4)
    End If
End If

[pinkcat]

oh thanx a lot that was a great help for me, haven't tried it yet but yes it is DAO application.

[pinkcat]

I have tried it but it gives a syntax error in where clause which I couldn't figure out...any ideas?

Thanks a lot for your help!

[MartinLiss]

Change

SQL = SQL & "MyField = " & retString(i) & " or "


to

SQL = SQL & "MyField = '" & retString(i) & "' or "

(I added two missing single quotes)

[pinkcat]

Yeah I tried that before adding single quotes but it still gives syntax error for where

[MartinLiss]

Can you show your actual code? BTW, if there can be spaces in the field names then that could be causing the problem. To get around that add square brackets [] around the field names.

[pinkcat]

Thanks a lot for you help Marty

SQL works fine now the problem was somewhere else in the code that I sorted now, one small change to the code u have given:

SQL = Left$(SQL, Len(SQL) - 4) minus 4 needs to be replaced by minus 3 as in the minus 4 it gets rid of ' too.

So thanks a lot for the help