Thread: Query searching returns incorrect results

After making some small chages to my search code I have found that my search is now returning incorrect results.

What is suppposed to happen is that when I search for a value in the volume field the search is supposed to return all results that match the full or partial search term (using "like" criteria in my query)

What is actually happening is that the search is only matching exact search criteria.
Any partial search requests result in the msgbox stating no matching results found.


This is the code that is found in the query for the volume field:

 Like [Forms]![search]![volume] & "*"

The above code is actually present on about 4 diff lines in the query tab.
I could post a screenshot later if reqd.

Yes the results returned are supposed to be any results that include the partial search criteria used in the search form.

Will have a look at that and post back.

Originally Posted by alansidman

Are you trying to get results that contain the criteria in any part of the volume? ie. you put in sugar in the search box and are looking for the results to be maple sugar syrup, sugar pops, candy sugars, etc. So sugar can be at the beginning, middle or end of the volume? If this is the case, then you need to add an "*" & right after Like, so that Access knows this. Right now it is only looking for volumes that begin with sugar (if you use my example)

Alan

Tried what you suggested and it still doesn't seem to work like it's supposed to .
This is what it looks like after I made the change:

Like "*" & [Forms]![search]![volume] & "*"

The strange thing is - the query line for the title field search is exactly the same as the old line for the volume search ... and it works perfectly as expected.

Like [Forms]![search]![title] & "*"

and the old volume field line

Like [Forms]![search]![volume] & "*"

I really don't understand why changing the data type on the volume field in the table would have caused this problem.

1. The data type was numeric
2. The data type is now text.
3. No it's just a text box.
4. irrelevant because i'm not using a combo box

ok will try it and get back to you

Originally Posted by alansidman

This ispart of the code behind your command button to run the search?



Are you able to post the entire code for that command button?

Also, are you running the query from the QBE or from a SQL Statement in VBA?

If from the QBE, would you post the entire SQL statement for the Query? Having bits and pieces doesn't give us the whole story.

Alan

Here is the code for the command button

Private Sub Find_Click()
Dim strCriteria As String
  
  If Len(Me.Volume & vbNullString) > 0 Then
    strCriteria = strCriteria & "[Volume] = '" & Me.Volume & "' AND "
  End If
  
  If Len(Me.Title & vbNullString) > 0 Then
    strCriteria = strCriteria & "[Movie Title] " & SQLQuote(Me.Title, True, True) & " AND "
  End If
  
  If Len(Me.Rating & vbNullString) > 0 Then
    strCriteria = strCriteria & "[Rating] = '" & Me.Rating & " ' AND "
  End If
  
  If Len(strCriteria) < 1 Then
    MsgBox ("Please enter search criteria before attempting to search the database")
  Else
    strCriteria = Left(strCriteria, Len(strCriteria) - 5)
    If DCount("*", "search", strCriteria) = 0 Then
      MsgBox "No movies matching your search. Please refine your search and try again."
      Exit Sub
    Else
      DoCmd.OpenForm "search results"
    End If
  End If
End Sub

As to your other question about how the query is being called, I must profess to not actually knowing that.
As far as I was aware the query is being run from within the above code or when the

DoCmd.OpenForm "search results"

line runs.

The out of memory error is only occurring when an attempt to open that particular form and it doesnt matter if the attempt is made via the button or by trying to dbl click the form in the object list on the left hand side.

Any other form opens perfectly well.

I can provide a copy of the entire project if it would make things easier ?

Originally Posted by davegugg

I just put a generic sql statement in.

Like "*" & [Forms]!]![search].[volume] & "*"

is not a valid SQL statement, it is just part of a WHERE clause. What is you whole SQL statement?

I'm sorry i'm still a little lost - I'm very new to all of this and have been using a lot of stuff that was provided to me after asking for assistance.

The line

Like "*" & [Forms]!]![search].[volume] & "*"

is what is found in the query section of my database project in the column for the volume field.

As previously stated I can provide a copy of the entire project if it will make things easier.

I'm not sure where the sql statement would be ... there is the query and there is code that operates after clicking a search button, that's all I know.