dear friends,

i'm using vb6 and access for my database application.
i used a data control to query a table in database with code below.

Private Sub cmdSpSearch_Click()
Dim findStr As String


 Title = "Special Search"
 findStr = InputBox("Please enter some description of " _
            & " the products.", Title)

Data1.RecordSource = "SELECT * FROM ITEMS WHERE Category = '" & dcboCat1.Text & "' AND SubCatName = '" & dcboSubCat1 & "'" _
   & " AND DESCRIPTION LIKE '*" & findStr & "*' "


        Data1.Refresh

If Data1.Recordset.NoMatch = True Then
        MsgBox "There is/are no product/s available " _
        & " with said description. "
        Exit Sub

        End If
end sub



is it possible to make a query out of this query and make another query out of the resulting query and and so on to narrow down search?

if yes, what would be the appropriate sql commands and approach.

thank you so much for any help.

raport

I am not exactly clear on what you are asking. Is it that you want to give your user the ability to narrow the search after viewing the results of the initial search?

If so, you merely need to store the criteria from the first search and then use it in building the next query with the addition of any criteria you allow the user to add for the second search. Of course, this could easily be extended to any number of iterations.

Woody Z http://www.learntoprogramnow.com

thanks for your reply. actually i'm making a select query on 1 table (Items) and 1 field (Description) with a memo type data in it. that means that there are quite a number of words in the said field.

i need to see if the description field contain the words being searched by the user. similar to a web search.

unfortunately i'm a beginner and as per your suggestion, would it be too much if you could guide me how to translate it to vb code.

thank you for your concern.

i got your idea and tried this manner.
i placed an input box and have the user place his search criteria separated by comma. the program looks for the words in the fields place them in the array and use the sql like and serries of and. for a while this suffices. here is the code:

Dim findStr As String
Dim x As Integer
Dim DescAry() As String
Dim SQLStrg, SQLStrg1 As String
Dim n, m, R, q, p As Integer
Dim Desc As String
'
On Error GoTo err


If dcboCat1.Text = "Category" Or dcboSubCat1.Text = "SubCategory" Then
 MsgBox "Please enter the correct category and sub-category. Thank you.", vbOKOnly, "Special Search"
 Exit Sub
 End If



 Title = "Special Search"
 findStr = InputBox("Please enter some description of " _
            & " the products each separated by a comma. Thank you ", Title)

' read the entry from the input box


    n = Len(Trim(findStr))

       'check if "," is properly placed
    q = 0
    For m = 1 To n

    If Mid(findStr, m, 1) = "," Then

    q = q + 1


    End If



    Next


If Mid(findStr, 1, 1) = "," Then
 MsgBox "Please correct the way description is written." & vbCrLf _
 & "Comma should be written in between description. Thank you.", vbOKOnly, "Notice!"

 Exit Sub

Else
   m = 1
   R = 1

   p = 1
   Desc = ""


        Do Until m = n + 1 ' main sensor

           Do Until Mid(findStr, m, 1) = "," ' forward sensor/counter (m)


              m = m + 1

               If m = n + 1 Then ' terminate loop
                 GoTo Line_1
                End If

             Loop

Line_1:
           Desc = Mid(findStr, p, m - p)

 ReDim Preserve DescAry(R)

           DescAry(R) = Trim(Desc)




           ' trailer sensor/counter (p)
           If m < (n + 1) Then
             Do Until Mid(findStr, p, 1) = ","

                 p = p + 1
             Loop

             R = R + 1
             m = m + 1
             p = p + 1
           Else

           GoTo line_2
           End If

      Loop
line_2:


 End If


SQLStrg1 = ""

For x = 1 To R

SQLStrg1 = SQLStrg1 + " AND DESCRIPTION LIKE '*" & DescAry(x) & "*'"

Next



SQLStrg = "SELECT * FROM ITEMS WHERE Category = '" & dcboCat1.Text & "' AND SubCatName = '" & dcboSubCat1 & "'"
SQLStrg = SQLStrg + SQLStrg1


'Exit Sub

Data1.RecordSource = SQLStrg



        Data1.Refresh

If Data1.Recordset.NoMatch = True Then
        MsgBox "There is/are no product/s available " _
        & " with said description. "
        Exit Sub

        End If
'counter

        Data1.Recordset.MoveLast
        Label3.Caption = Data1.Recordset.RecordCount
Exit Sub
err:

   MsgBox "There is no record for that description. Thank you.", vbOKOnly, "Special Search"
Label3.Caption = "0"
End Sub

thank you again.

raport