Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > SQL Language
|
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old November 8th, 2006, 04:58 AM
Registered User
 
Join Date: Nov 2006
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default how to make a query from an existing query

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

 
Old November 8th, 2006, 12:04 PM
Friend of Wrox
 
Join Date: May 2006
Posts: 643
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old November 8th, 2006, 08:48 PM
Registered User
 
Join Date: Nov 2006
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.




 
Old November 13th, 2006, 08:59 PM
Registered User
 
Join Date: Nov 2006
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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






Similar Threads
Thread Thread Starter Forum Replies Last Post
how to open an existing query in VBA michael193nj Access VBA 4 March 26th, 2008 05:09 PM
How can I make this query michael193nj Access 3 March 19th, 2008 05:13 PM
Make Table Query CongoGrey Access 2 June 1st, 2007 06:22 AM
How to make a query to google? tqmk1982 BOOK: Beginning Java 2 1 March 26th, 2004 02:12 PM
Error on Make-Table Query In Union Query rylemer Access 1 August 20th, 2003 07:42 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.