[RESOLVED] Error in code

[osemollie]

Am trying to search my database using dates. Where am i going wrong? My code is

If chkPurchased.Enabled Then sWhere = sWhere & sKeyWord & "PurchaseDate >= & "#" & dtpPurchaseAfter.Value & "#" AND PurchaseDate <= & "#" & dtpPurchaseAfter.Value & "#"

Thanks.

[Hack]

What error are you getting?

[Pasvorto]

If you are going against a SQL database, you need to change the # to an apostrophe

[si_the_geek]

Please always tell us what the error is, even if you think it is obvious.

After inspection I can see that you haven't built the string properly, it should be like this:

If chkPurchased.Enabled Then sWhere = sWhere & sKeyWord & "PurchaseDate >= #" & dtpPurchaseAfter.Value & "# AND PurchaseDate <= #" & dtpPurchaseAfter.Value & "#"

[osemollie]

Please always tell us what the error is, even if you think it is obvious.

After inspection I can see that you haven't built the string properly, it should be like this:

If chkPurchased.Enabled Then sWhere = sWhere & sKeyWord & "PurchaseDate >= #" & dtpPurchaseAfter.Value & "# AND PurchaseDate <= #" & dtpPurchaseAfter.Value & "#"

My apologies for not giving all the info. I have tried the above but came up with no results at all yet i chose dates that fall within the scope.

My complete code is:

rivate Sub cmdSearch_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, movie_inventory.PurchaseDate, movieInfo.movie_year, country.country, language1.language1, audienceRating.rating " _
        & "FROM audienceRating INNER JOIN (language1 INNER JOIN " _
        & "(country INNER JOIN ((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) ON country.countryId = movieInfo.countryId)" _
        & "ON language1.languageId = movieInfo.languageId) ON audienceRating.ratingId = movieInfo.ratingId"
 
  If txtSearch.Text <> "" And chkMatchcase.Value Then sWhere = sWhere & sKeyWord & "title Like '%" & txtSearch.Text & "%' "
  If txtSearch.Text <> "" And chkMatchWhole.Value Then sWhere = sWhere & sKeyWord & "title = '" & txtSearch.Text & "' "
  If txtReference.Text <> "" Then sWhere = sWhere & sKeyWord & "reference = '" & txtReference.Text & "' "
  If cboGenre.Text <> "" Then sWhere = sWhere & sKeyWord & "genre = '" & cboGenre.Text & "' "
  If cboSubgenre.Text <> "" Then sWhere = sWhere & sKeyWord & "genre = '" & cboSubgenre.Text & "' "
  If cboCountry.Text <> "" Then sWhere = sWhere & sKeyWord & "country = '" & cboCountry.Text & "' "
  If cboFormat.Text <> "" Then sWhere = sWhere & sKeyWord & "strKey = '" & cboFormat.Text & "' "
  If cboYear.Text <> "" Then sWhere = sWhere & sKeyWord & "movie_year = '" & cboYear.Text & "' "
  If chkPurchased.value Then sWhere = sWhere & sKeyWord & "PurchaseDate >= #" & dtpPurchaseAfter.Value & "# AND PurchaseDate <= #" & dtpPurchaseAfter.Value & "#"
 
  If sWhere <> "" Then
    sSQL = sSQL & " WHERE " & Mid(sWhere, Len(sKeyWord) + 1)
  End If
 
  rst.Open sSQL, cnAddMovie, adOpenKeyset, adLockPessimistic, adCmdText
     
    Call PopulateList(ListView1, rst)
    
End Sub

Thanks for your help

[Pasvorto]

Have you tried this:

If chkPurchased.value Then sWhere = sWhere & sKeyWord & "PurchaseDate >= #" & cdate(dtpPurchaseAfter.Value) & "# AND PurchaseDate <= #" & cdate(dtpPurchaseAfter.Value) & "#"

[randem]

Do a

Debug.Print YourSQLStatement

then Post that....

[osemollie]

Do a

Debug.Print YourSQLStatement

then Post that....

This is what I get:

SELECT movieInfo.title, MediaType.StrKey, genre.genre, movieInfo.movie_year, country.country, language1.language1, movie_inventory.PurchaseDate, audienceRating.rating FROM audienceRating INNER JOIN (language1 INNER JOIN (country INNER JOIN ((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) ON country.countryId = movieInfo.countryId)ON language1.languageId = movieInfo.languageId) ON audienceRating.ratingId = movieInfo.ratingId WHERE PurchaseDate >= #7/1/2006 1:36:12 PM# AND PurchaseDate <= #7/1/2006 1:36:12 PM#

[si_the_geek]

Ok... first of all, here's the same SQL statement in a way we can read it:

SELECT    movieInfo.title,
          MediaType.StrKey,
          genre.genre,
          movieInfo.movie_year,
          country.country,
          language1.language1,
          movie_inventory.PurchaseDate,
          audienceRating.rating

FROM      audienceRating
          INNER JOIN ( language1
          INNER JOIN ( country
          INNER JOIN ( ( 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 ) 
            ON country.countryId = movieInfo.countryId ) 
            ON language1.languageId = movieInfo.languageId ) 
            ON audienceRating.ratingId = movieInfo.ratingId

WHERE     PurchaseDate > = #7/1/2006 1:36:12 PM#
          AND PurchaseDate < = #7/1/2006 1:36:12 PM#

As the Where clause is twice comparing the same field to the same value (just with different signs), it is effectively:

WHERE     PurchaseDate = #7/1/2006 1:36:12 PM#

..which I'm guessing isn't what you wanted. I'm assuming that you should either be using a different value for one of them, or a different field.

[osemollie]

Am actually trying to limit my search to a given period. Like in this case let us say I want all movies that were bought between July 1, 2006 and September 10, 2006. All these information is stored in one field PurchaseDate. How can I query this?

Thanks.

[si_the_geek]

You will obviously need to get two dates from the client, one for the start of the date range, and one for the end of it. For this you could use two controls, eg: dtpPurchaseAfterStart and dtpPurchaseAfterEnd

You can then put these into the appropriate places in the "If chkPurchased.value Then" line.

[osemollie]

You will obviously need to get two dates from the client, one for the start of the date range, and one for the end of it. For this you could use two controls, eg: dtpPurchaseAfterStart and dtpPurchaseAfterEnd

You can then put these into the appropriate places in the "If chkPurchased.value Then" line.

Thanks si. I actually have traced where the problem was. As you rightly put i needed two controls to define start/end date. But when you look at my code above, I made a mistake and used the same control in my WHERE clause. It is now sorted out and working.

Thanks all of you.