making a query from an existing query

[raport]

dear friends,

i'm new to this forum and posted this thread in the MS Access forum which i think is not appropriate. so i decided to post it here which i think is the closest place for this. sorry for whatever clatter this thread may make.

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.

initially here's what i did.

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


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.

 

[lespaul]

Do you mean you want to accomplish something similar to Google's 'Search within results' function?

Have the user enter a keyword and search for that and then allow additional key words to be entered and search again for those within the original result set?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[fordtran]

Try something like :

S$ = "Select * from items where instr(description ,'" & findstr & "')>0"


fordtran

 

[raport]

dear friends,

thank you for your suggestions.

initially i did it this way after your suggestions.
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

 

[PHV]

Here a simpler way:

...
findStr = InputBox("Please enter some description of " _
 & " the products each separated by a comma. Thank you ", Title)
DescAry = Split(findStr, ",")
SQLStrg = "SELECT * FROM ITEMS WHERE Category='" & dcboCat1.Text & "' AND SubCatName='" & dcboSubCat1 & "'"
For x = 0 To UBound(DescAry)
  SQLStrg1 = Trim(DescAry(x))
  If SQLStrg1 <> "" Then
    SQLStrg = SQLStrg & " AND Description LIKE '*" & SQLStrg1 & "*'"
  End If
Next
Data1.RecordSource = SQLStrg
...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[raport]

thank you for your simplifying the codes.