Implementing search function

Printable View

Jul 19th, 2006, 09:11 AM
osemollie

1 Attachment(s)

Implementing search function

Am trying to implement a search functionality in my movie application. I want to enable people to search the database (MSACCESS 2000) using either the title, actors, genre, language, format, loan_dates etc. Attached is my GUI but I dont know how well I can code inorder to get desired results. On clicking the search button, a new form will pop-up and the movies meeting the criteria are listed in a ListView. Upon which, one can click or double click a selected item and it shows the details of this movie. The search will include AND/OR operators.
Jul 19th, 2006, 10:09 AM
si_the_geek

Re: Implementing search function

You just need to build the SQL statement based on the control values. As the SQL statement is just a string, you can use normal string manipulation to do that.

For an example, see the "How about a search button?" section of this FAQ thread.
Jul 19th, 2006, 10:41 AM
Al42

Re: Implementing search function

You'll want a criterion choice for each search term. IOW, Genre = 'XXX' AND Country = 'YYY' OR Language = 'ZZZ', and you'll want to be able to group with parentheses - do you want a search on (Genre = 'XXX' AND Country = 'YYY') OR Language = 'ZZZ' or do you want Genre = 'XXX' AND (Country = 'YYY' OR Language = 'ZZZ')?

The GUI to produce a real search statement can be very involved, and beyond the capability of most users to understand. It's always a trade-off - do you leave the user with not very much capability, or do you overwehelm the user with capability he can't understand?
Jul 19th, 2006, 11:01 AM
osemollie

Re: Implementing search function

AL,

I'd rather leave the user with not very much capability. Looking at the GUI which fields then should I drop, in your view?

Re: Implementing search function

i dont know if this would work, but something along these lines?

VB Code:

Private Sub cmdSearch_Click(sSQL As String)
    'first selection from keyword level operator
    If cboKeyword.ListIndex = 0 Then
        sSQL = "SELECT * FROM Table_Name "
        sSQL = sSQL & "WHERE Title_Column = '" & cboTitle.Text & ", "
        sSQL = sSQL & "AND Actors_Column = '" & cboActors.Text & ", "
        sSQL = sSQL & "AND Genre_Column = '" & cboGenre.Text & ", "
        sSQL = sSQL & "AND Lang_Column = '" & cboLanguage.Text & ", "
        sSQL = sSQL & "AND Format_Column = '" & cboFormat.Text & ", "
        sSQL = sSQL & "AND Loan_Dates_Column = '" & cboLoan_Dates.Text & "'"
        cn.Execute sSQL
    'second selection from keyword level operator
    ElseIf cboKeyword.ListIndex = 1 Then
        sSQL = "SELECT * FROM Table_Name "
        sSQL = sSQL & "WHERE Title_Column = '" & cboTitle.Text & ", "
        sSQL = sSQL & "OR Actors_Column = '" & cboActors.Text & ", "
        sSQL = sSQL & "OR Genre_Column = '" & cboGenre.Text & ", "
        sSQL = sSQL & "OR Lang_Column = '" & cboLanguage.Text & ", "
        sSQL = sSQL & "OR Format_Column = '" & cboFormat.Text & ", "
        sSQL = sSQL & "OR Loan_Dates_Column = '" & cboLoan_Dates.Text & "'"
        cn.Execute sSQL
    Else
        sSQL = "SELECT * FROM Table_Name "
        sSQL = sSQL & "WHERE NOT Title_Column = '" & cboTitle.Text & ", "
        sSQL = sSQL & "AND NOT Actors_Column = '" & cboActors.Text & ", "
        sSQL = sSQL & "AND NOT Genre_Column = '" & cboGenre.Text & ", "
        sSQL = sSQL & "AND NOT Lang_Column = '" & cboLanguage.Text & ", "
        sSQL = sSQL & "AND NOT Format_Column = '" & cboFormat.Text & ", "
        sSQL = sSQL & "AND NOT Loan_Dates_Column = '" & cboLoan_Dates.Text & "'"
        cn.Execute sSQL
End Sub

Quote:

Originally Posted by osemollie

Am trying to implement a search functionality in my movie application. I want to enable people to search the database (MSACCESS 2000) using either the title, actors, genre, language, format, loan_dates etc. Attached is my GUI but I dont know how well I can code inorder to get desired results. On clicking the search button, a new form will pop-up and the movies meeting the criteria are listed in a ListView. Upon which, one can click or double click a selected item and it shows the details of this movie. The search will include AND/OR operators.

Jul 19th, 2006, 11:22 AM
osemollie

Re: Implementing search function

Thanks BrailleSchool, you have given me an idea of how I should work it out. Let me try it out and will keep you posted.
Jul 19th, 2006, 11:23 AM
BrailleSchool

Re: Implementing search function

Quote:

Originally Posted by osemollie

Thanks BrailleSchool, you have given me an idea of how I should work it out. Let me try it out and will keep you posted.

youre welcome

Re: Implementing search function

If you don't want to have "empty" textboxes being part of the search, you will need to use If statements as in the example I linked to.

By the way, BrailleSchool's example could be simplified to this:

VB Code:

Dim sKeyWord As String
 
  Select Case cboKeyword.ListIndex
  Case 0:  sKeyWord = "AND "
  Case 1:  sKeyWord = "OR "
  Case 2:  sKeyWord = "AND NOT "
  End Select
   
  sSQL = "SELECT * FROM Table_Name "
  sSQL = sSQL & "WHERE Title_Column = '" & cboTitle.Text & "' "
  sSQL = sSQL & sKeyWord & "Actors_Column = '" & cboActors.Text & "' "
  sSQL = sSQL & sKeyWord & "Genre_Column = '" & cboGenre.Text & "' "
  sSQL = sSQL & sKeyWord & "Lang_Column = '" & cboLanguage.Text & "' "
  sSQL = sSQL & sKeyWord & "Format_Column = '" & cboFormat.Text & "' "
  sSQL = sSQL & sKeyWord & "Loan_Dates_Column = '" & cboLoan_Dates.Text & "'"
  cn.Execute sSQL

Re: Implementing search function

i was thinking about that but didnt know how to do it. :) now i do

Quote:

Originally Posted by si_the_geek

VB Code:

Dim sKeyWord As String
 
  Select Case cboKeyword.ListIndex
  Case 0:  sKeyWord = "AND "
  Case 1:  sKeyWord = "OR "
  Case 2:  sKeyWord = "AND NOT "
  End Select
   
  sSQL = "SELECT * FROM Table_Name "
  sSQL = sSQL & "WHERE Title_Column = '" & cboTitle.Text & "' "
  sSQL = sSQL & sKeyWord & "Actors_Column = '" & cboActors.Text & "' "
  sSQL = sSQL & sKeyWord & "Genre_Column = '" & cboGenre.Text & "' "
  sSQL = sSQL & sKeyWord & "Lang_Column = '" & cboLanguage.Text & "' "
  sSQL = sSQL & sKeyWord & "Format_Column = '" & cboFormat.Text & "' "
  sSQL = sSQL & sKeyWord & "Loan_Dates_Column = '" & cboLoan_Dates.Text & "'"
  cn.Execute sSQL

Jul 19th, 2006, 01:18 PM
Al42

Re: Implementing search function
Quote:
Originally Posted by si_the_geek

VB Code:

sSQL = sSQL & "WHERE Title_Column = '" & cboTitle.Text & "' "
sSQL = sSQL & sKeyWord & "Actors_Column = '" & cboActors.Text & "' "
...
Si, aren't you going to get an incorrect SQL statement if any of the comboboxes is blank?
Jul 19th, 2006, 01:30 PM
si_the_geek

Re: Implementing search function

Nope, the SQL statement will be perfectly valid - but the relevant part of the Where clause will only match with rows where the field is blank (but not Null).

(if the field was Numeric, or a date, a blank value would indeed make it an invalid SQL statement)

In order to ignore any "blank" comboboxes, you need to use an If statement as in the example I keep on refering too. ;)

Re: Implementing search function

si_the_geek, I have been to the thread but am a bit slow, am still not able to relate your code above and the one below, which is from the thread. Someone help me understand better. In my case, I dont want to have "empty" textboxes.

VB Code:

Dim strSQL as String, strWhere as String
    'build the SQL statement based on what the user typed in txtSearch and txtAnotherSearch
  strSQL = "SELECT * FROM tbl_master"
                   '(find the conditions)
  If txtSearch.Text <> "" Then strWhere = strWhere & " AND Field2 = " & Val(txtSearch.Text)
  If txtAnotherSearch.Text <> "" Then strWhere = strWhere & " AND Field7 = " & Val(txtAnotherSearch.Text)
  If strWhere <> "" Then    '(put the conditions into the SQL statement, without the first And)
    strSQL & " WHERE " & Mid(strWhere, 5)
  End If

Re: Implementing search function

No problem, this kind of thing can be a bit daunting if you haven't done it before!

It helps if you can write the SQL for each situation, for example if you were only looking for Genre the SQL might look like this:
VB Code:
strSQL = "SELECT * FROM table_name WHERE genre = '" & cboGenre.Text & "'"
The idea is to build the SQL statement based on the control values, only using the conditions that have been specified. To do this you start of with the known part ("SELECT * FROM table_name"), and then add the conditions on to it.

As you don't know which condtions will definitely be used (if any), you cant just place the "Where" into the known part, so we build the Where clause in a separate string, and join it on when it is finished.

BrailleSchool's idea is a great part, and this is how you would implement it all together:
VB Code:
Dim sKeyWord As String    'Find the keyword to use
  Select Case [u]cboKeyword[/u].ListIndex   '(change to the name of your keyword combo, and change the keywords below to a different order if needed)
  Case 0:  sKeyWord = " AND "
  Case 1:  sKeyWord = " OR "
  Case 2:  sKeyWord = " AND NOT "
  End Select
 
Dim strSQL as String, strWhere as String
    'build the SQL statement based on what the user typed in txtSearch and txtAnotherSearch
  strSQL = "SELECT * FROM [u]table_name[/u]"  '(change to the name of your table)
 
                   '(find the conditions)
'(this line is basically the same as the example above, but only if the text has been set)
  If cboGenre.Text <> "" Then strWhere = strWhere & sKeyWord & "Genre = '" & cboGenre.Text & "'"
 
  If txtAnotherSearch.Text <> "" Then strWhere = strWhere & sKeyWord & "NumericField = " & Val(txtAnotherSearch.Text)
 
                   '(put the conditions into the SQL statement, without the first keyword)
  If strWhere <> "" Then    
    strSQL & " WHERE " & Mid(strWhere, len(sKeyWord) + 1)
  End If
The most awkard part is "find the conditions", which you should probably start off with just one condition, and test it. You can then add one at a time, and test each one as you go (on it's own, and then with the ones that are already working).

Jul 20th, 2006, 10:25 AM
osemollie

Re: Implementing search function

Thanks for the clarification. Lemme dive into it and will keep you posted.
Jul 21st, 2006, 06:56 AM
osemollie

Re: Implementing search function

I decided to take si_the_geek's advice and try out indivudual codes to confirm whether they work or not before attempting the COMBINATIONS. Looking at my GUI graphic above, you will realise that I want to allow the user to search movie eg loaned out between specified dates. How can I code that?
- On clicking the search button, I want to open a new window and inside this window, the Results get shown in a ListView control. How to?
- As Al42 had pointed earlier, I get an error when i try the searching while the combo boxes are empty.

Your help is much appreciated.

Re: Implementing search function

I have the following code:-

VB Code:

Private Sub cmdSearch_Click()
Dim strSQL As String
Set rsMovie = New ADODB.Recordset
Dim li As ListItem
 
 
With rsMovie
.Open "SELECT movie_inventory.movieId, movieInfo.title, MediaType.StrKey, trans1.TransDate, TransDetails.ItemDueDate FROM MediaType INNER JOIN (movieInfo INNER JOIN ((trans1 INNER JOIN TransDetails ON trans1.transactID = TransDetails.transactID) INNER JOIN movie_inventory ON TransDetails.movie_inventoryId = movie_inventory.movie_inventoryId) ON movieInfo.movieId = movie_inventory.movieId) ON MediaType.media_catId = movie_inventory.media_catId WHERE TransDate >= " & dtpLoanBefore.Value & "", cnAddMovie, adOpenForwardOnly, adLockReadOnly
 
Do While Not .EOF
Set li = ListView1.ListItems.Add(Text:=.Fields("title") & vbNullString)
    li.ListSubItems.Add Text:=.Fields("StrKey") & vbNullString
    li.ListSubItems.Add Text:=.Fields("TransDate") & vbNullString
    li.ListSubItems.Add Text:=.Fields("ItemDueDate") & vbNullString
    .MoveNext
Loop
'.Close
End With
 
 
 
Set rsMovie = Nothing
 
End Sub

The loan checkbox is known as chkLoan. Where in my code should I place my IF ... Then .... End If code?

Jul 21st, 2006, 08:55 AM
si_the_geek

Re: Implementing search function

The .Open immediately opens the recordset using the SQL you provide, so you cannot give it part of the SQL, you need all of it. So instead of having:

.Open "SELECT ....
You need to use:

.Open strSQL

Your SQL statement needs to be built in strSQL as I showed you, everything before the WHERE in your .Open line needs to be in the initial setup of strSQL (replacing strSQL = "SELECT * FROM table_name" from my example), and all control values (such as your current Where clause) need to be in If statements as in my example.
Jul 21st, 2006, 09:37 AM
osemollie

Re: Implementing search function
sorry, what does this line do in the code in relation to the rest?
VB Code:
```
strSQL = strSQL & " WHERE " & Mid(strWhere, Len(sKeyWord) + 1)
```
Jul 21st, 2006, 09:45 AM
si_the_geek

Re: Implementing search function

All of the conditions (in the example at least, and presumably in your project too) are 'optional', so you cannot tell which will be the first one - so we just put the same keyword before each one (then it will be between each different item, but also at the start instead of "WHERE"). By the time this line of code is reached, strWhere may contain this:
" AND Genre = 'genre' AND NumericField = 7"

..which means that in order to create a proper SQL statement we need to remove the first keyword (which is what the Mid does), and replace it with WHERE, then append that to the end of the existing SQL.
Jul 21st, 2006, 10:38 AM
Al42

Re: Implementing search function

I just build a strWhere. If it's blank it gets " Where ", if not it gets " And ". Then it gets the condition being tested for. After all the conditionals strSQL = strSQL & strWhere.
Jul 21st, 2006, 10:45 AM
si_the_geek

Re: Implementing search function

That's basically the same as the approach I've suggested - except my version makes the 'repeated' code half the size (so easier to write/read/maintain), and probably faster (as you dont repeatedly check for a blank strWhere, only do 1 Mid at the end).

The speed isn't really an issue tho, as the whole code will run in a very small amount of time anyway.
Jul 21st, 2006, 10:57 AM
osemollie

Re: Implementing search function

Quote:

Nope, the SQL statement will be perfectly valid - but the relevant part of the Where clause will only match with rows where the field is blank (but not Null).

(if the field was Numeric, or a date, a blank value would indeed make it an invalid SQL statement)

Some fields of mine are numeric and when left blank, I get an error in the SQL code. What is the best way around it?

- Just to mention, On clicking the search button, I want to open a new form and on this form, the results get shown in a ListView control . How to do I do that?
Jul 21st, 2006, 11:03 AM
si_the_geek

Re: Implementing search function

Quote:

Some fields of mine are numeric and when left blank, I get an error in the SQL code. What is the best way around it?

Again, use If statements as shown in my example - you should not be adding any "blank" conditions to the SQL statement.

If you can't work out how to do that, show us the current code.

Quote:

Just to mention, On clicking the search button, I want to open a new form and on this form, the results get shown in a ListView control . How to do I do that?

Basically as you posted above (assuming that code works for the same form), but with code to load the form first (eg: Form1.Load), and prefixing the control with the form name (eg: Set li = Form1.ListView1.ListItems.Add ....).

Re: Implementing search function

Still having bit of a problem with numeric field being blank. My code is:

VB Code:

Private Sub cmdTesting_Click()
Dim sKeyWord As String
Set rst = New Recordset
   
  Select Case cboField.ListIndex
  Case 0:  sKeyWord = "AND "
  Case 1:  sKeyWord = "OR "
  Case 2:  sKeyWord = "AND NOT "
  
  End Select
     sSQL = "SELECT movieInfo.title, MediaType.StrKey, genre.genre, movieInfo.movie_year FROM (MediaType INNER JOIN (movieInfo INNER JOIN movie_inventory ON movieInfo.movieId = movie_inventory.movieId) ON MediaType.media_catId = movie_inventory.media_catId) INNER JOIN genre ON movieInfo.GenreID = genre.genreId "
  sSQL = sSQL & "WHERE title = '" & txtSearch.Text & "' "
  sSQL = sSQL & sKeyWord & "genre = '" & cboGenre.Text & "' "
  sSQL = sSQL & sKeyWord & "StrKey = '" & cboFormat.Text & "' "
  sSQL = sSQL & sKeyWord & "movie_year = " & cboYear.ListIndex & " "
  
   rst.Open sSQL, cnAddMovie, adOpenKeyset, adLockPessimistic, adCmdText
     'show the data
 
Debug.Print sSQL
    
    
    Call PopulateList(ListView1, rst)
End Sub

- I have not gotten exactly how am to handle the loan dates. If the loan checkbox is checked, then i specify the range of dates to search.

Re: Implementing search function

You seem to have ignored several important parts of each line in the "'(find the conditions)" part of my example, and left out the vital "'(put the conditions into the SQL statement, without the first keyword)" section ;)

Here's a corrected version:

VB Code:

Private Sub cmdTesting_Click()
Dim sKeyWord As String
Dim sSQL as String, sWhere as String
  Set rst = New Recordset
 
  Select Case cboField.ListIndex
  Case 0:  sKeyWord = "AND "
  Case 1:  sKeyWord = "OR "
  Case 2:  sKeyWord = "AND NOT "  
  End Select
 
  sSQL = "SELECT movieInfo.title, MediaType.StrKey, genre.genre, movieInfo.movie_year " _
       & "FROM (MediaType INNER JOIN " _
              & "(movieInfo INNER JOIN " _
                & "movie_inventory ON movieInfo.movieId = movie_inventory.movieId) " _
              & "ON MediaType.media_catId = movie_inventory.media_catId) " _
            & "INNER JOIN genre ON movieInfo.GenreID = genre.genreId "
 
                   '(find the conditions)
  If txtSearch.Text <> "" Then sWhere = sWhere & sKeyWord & "title = '" & txtSearch.Text & "' "
  If cboGenre.Text <> "" Then sWhere = sWhere & sKeyWord & "genre = '" & cboGenre.Text & "' "
  If cboFormat.Text <> "" Then sWhere = sWhere & sKeyWord & "strKey = '" & cboFormat.Text & "' "
  If cboYear.ListIndex <> "" Then sWhere = sWhere & sKeyWord & "movie_year = " & cboYear.ListIndex
'...
  
                   '(put the conditions into the SQL statement, without the first keyword)
  If sWhere <> "" Then    
    sSQL & " WHERE " & Mid(sWhere, len(sKeyWord) + 1)
  End If
 
 
 
  rst.Open sSQL, cnAddMovie, adOpenKeyset, adLockPessimistic, adCmdText
     'show the data
 
Debug.Print sSQL
    
    
    Call PopulateList(ListView1, rst)
End Sub

For the Loan Dates you may want to set up the condition like this (with an appropriate If before it):

VB Code:

sWhere = sWhere & sKeyWord & "(LoanDate >= #" & txtLoadStart.Text & "# AND LoanDate >= #" & txtLoadEnd.Text & "#)"

Jul 21st, 2006, 01:09 PM
osemollie

Re: Implementing search function

si_the_geek, I really appreciate your patience and toleranace. Am determined to learn and this is proving a wonderful experience. For real, one cannot find your kind of teaching in a book. Thanks.

- Am faced with another dilema. All the fields except the loan/return controls search a different query (movieInfo, MediaType, genre etc tables). But the loan/return controls search using a different query all together (generated from order and order_details tables). How can I ensure that a specific query runs depending on the controls used?
- Looking at the GUI, my intention of using the field combobox was to search the database using the selected/filled in values. My choice for keyword level search was to enable a user to search using keywords typed in the textbox. For example I might have a movie title as 'Twins' and another one as 'The Siamese Twins', the keyword search should allow one to search using 'Twins' and all the two movies should come up.
Jul 21st, 2006, 02:51 PM
Al42

Re: Implementing search function
Quote:

Originally Posted by osemollie

- Am faced with another dilema. All the fields except the loan/return controls search a different query (movieInfo, MediaType, genre etc tables). But the loan/return controls search using a different query all together (generated from order and order_details tables). How can I ensure that a specific query runs depending on the controls used?
VB Code:
```
If chkLoan.Value = 1 Then
...
ElseIf chkReturn.Value = 1 Then
...
Else
<search code for comboboxes>
End If
```
I'd also disable (<control>.Enabled = False) the controls you're not going to be doing the search on if either one is checked, so the user knows not to set things that won't be searched on.

Quote:

- Looking at the GUI, my intention of using the field combobox was to search the database using the selected/filled in values. My choice for keyword level search was to enable a user to search using keywords typed in the textbox. For example I might have a movie title as 'Twins' and another one as 'The Siamese Twins', the keyword search should allow one to search using 'Twins' and all the two movies should come up.

Also using the comboboxes, or ignoring the comboboxes?
Jul 21st, 2006, 03:19 PM
si_the_geek

Re: Implementing search function
I'm not quite sure what you mean from your "Twins" example, it may be as simple as changing from this:
VB Code:
```
... & "title = '" & txtSearch.Text & "' "
```
..to this:
VB Code:
```
... & "title Like '%" & txtSearch.Text & "%' "
```
That will find any rows where the Title field contains the contents of txtSearch.Text at any position in the field (so "Twins" would match both of your examples, but it would also match things like "Twinset").

Quote:

si_the_geek, I really appreciate your patience and toleranace. Am determined to learn and this is proving a wonderful experience. For real, one cannot find your kind of teaching in a book. Thanks.

Thanks. :)

I aim to help people become better programmers, rather than simply throwing examples at them (but I do that too!). It's good to see people trying to learn. :thumb:
Jul 24th, 2006, 06:44 AM
osemollie

Re: Implementing search function

Looking at my GUI above, my movie application allows one to borrow movies for:

If VCD =3 days
DVD = 2 days
VHS = 4

How can I ensure that if one borrows a VHS for example, the checkout date is increased automatically to 4 days from the day of checkout? And there after I will have an admin form where I want to allow the user to determine the number of days allowed, instead of the default that I have set.

How can I code that?

Thanks.
Jul 24th, 2006, 06:47 AM
Hack

Re: Implementing search function

What control are you using to tell the program what kind of movie it is? Option buttons? Check boxes?
Jul 24th, 2006, 07:57 AM
osemollie

Re: Implementing search function

Sorry, I have moved the question to the appropriate thread "Which control can I use" Look at the GUI there. Am using comboboxes to determine types.
Jul 24th, 2006, 11:08 AM
Al42

Re: Implementing search function

Quote:

Originally Posted by osemollie

How can I ensure that if one borrows a VHS for example, the checkout date is increased automatically to 4 days from the day of checkout?

Use the Datediff function.

Quote:

And there after I will have an admin form where I want to allow the user to determine the number of days allowed, instead of the default that I have set.

How can I code that?

Keep the number of days for each in your database - in a table named Expiration, maybe? The program looks up the 3 expiration times when it runs and keeps them in global variables. Another function allows someone with the correct password to change the values, both in the global variables and in the data table (at the same time - change in both or neither).
Jul 24th, 2006, 11:14 AM
osemollie

Re: Implementing search function

Hi AL42, thanks for coming to my rescue. Look at : http://www.vbforums.com/showthread.p...01#post2556401

to see the GUI am using. Am a bit not sure of how to implement your solution. The expiry dates are saved in a table Mediatype wshose fields are: mediaTypeId, mediaType, DaysForBorrowing.

Thanks
Jul 24th, 2006, 01:41 PM
Al42

Re: Implementing search function

Just get DaysForBorrowing and use it in a DateAdd - dtReturnDate = DateAdd("d", DaysForBorrowing, Now) and write a little utility that allows someone with the correct password to change DaysForBorrowing in the table (a simple Update SQL statement).

All times are GMT -5. The time now is 11:16 AM.