Tukukkk

I want to search in access field.
i have used (like "*") and this gives me a good result but I want to keep searching when the user write "space" between the words in the content.
please help

Use an array:

Dim Words As Variant

Dim Word  As String

Dim Index As Integer



Words = Split("Words to search", " ")



For Index = LBound(Words) To UBound(Words)

    Word = Trim(Words(Index))

    If Word <> "" Then    

        ' Run your search routine using the word in Word.

    End If

Next

When using a search box, I am of the opinion that additional search terms should be applied using an AND logic so as to narrow the search, rather than broaden it (as would be obtained from OR logic).

Hence, I would suggest something along the lines of the following:

Function SearchQuery(strFind As String) As DAO.QueryDef

    Dim arrTrm() As String

    Dim arrCrt() As String

    Dim strCrt As String

    Dim lngIdx As Long



    arrTrm = Split(strFind, " ")



    ReDim arrCrt(UBound(arrTrm))

    For lngIdx = LBound(arrTrm) To UBound(arrTrm)

        arrCrt(lngIdx) = "par" & CStr(lngIdx)

    Next lngIdx



    Dim qdfQry As DAO.QueryDef

    Dim rstRst As DAO.Recordset

    Set SearchQuery = CurrentDb.CreateQueryDef _

    ( _

        "", _

        "select * from table1 where table1.field1 like " & _

        Join(arrCrt, " and table1.field1 like ") _

    )

    For lngIdx = LBound(arrTrm) To UBound(arrTrm)

        SearchQuery.Parameters(lngIdx) = "*" & arrTrm(lngIdx) & "*"

    Next lngIdx

End Function

The above will construct & return a parameterised unnamed query definition which is configured to search the content of field field1 of table table1 for all words supplied in the space-delimited string.

For example:

Function test()

    With SearchQuery("Your Search Query").OpenRecordset

        If Not .EOF Then

            .MoveFirst

            Do Until .EOF

                Debug.Print !Field1

                .MoveNext

            Loop

        End If

        .Close

    End With

End Function

Such words could appear in the field content in any order, but must all appear in the content.

This is of course but one way to accomplish this - for your particular application it is likely easier to update the SQL of a saved query, or perform some operation on the results of the query within this function, but I'll leave that for you to decide.

Where the user puts spaces in their search string, replace each space with " OR " before you use it to search.

so they type: "apple orange"
you add the " OR " to search for apple OR orange