#  Other Applications & Softwares  > Access Tables & Databases >  >  Query searching returns incorrect results

## vampyr07au

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: 



```
Please Login or Register  to view this content.
```


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

----------


## 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

----------


## vampyr07au

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.

----------


## vampyr07au

> 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: 



```
Please Login or Register  to view this content.
```


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. 




```
Please Login or Register  to view this content.
```


and the old volume field line



```
Please Login or Register  to view this content.
```


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

----------


## alansidman

A couple of questions.What was your data type before the changewhat is your data type noware you using a combo box to make your selectionif you are using a combo box, is the related lookup table linked to your query
Alan

----------


## vampyr07au

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

----------


## alansidman

I don't have a definitive answer so I am grasping for straws at this time.  Try changing the second '!' in your Like criteria to a '.'  The one between the form name and the field name.  I know that they are sometimes interchangeable and really shouldn't make a difference, but sometimes Access does some strange things also.

Alan

----------


## vampyr07au

ok will try it and get back to you

----------


## vampyr07au

Did you mean to change this:



```
Please Login or Register  to view this content.
```


to this: 



```
Please Login or Register  to view this content.
```


I have done this but am now getting a runtime error that will not allow me to test it. 

Runtime error 2004: There isn't enough memory to perform the operation. Close unnecessary programs and try again

The highlighted debug line is:


```
Please Login or Register  to view this content.
```




I did try and add more tables, fields and forms to the database but surely access would be able to cope with modification of the files in this fashion ?

----------


## alansidman

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





> The highlighted debug line is: 
> Code:
> DoCmd.OpenForm "search results"



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

----------


## vampyr07au

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



Here is the code for the command button



```
Please Login or Register  to view this content.
```



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 



```
Please Login or Register  to view this content.
```


 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 ?

----------


## davegugg

Can you try storing the entire search phrase as a variable so you can check the variable before running the query?  Something like this:




```
Please Login or Register  to view this content.
```


Then use 


```
Please Login or Register  to view this content.
```


 and the immediate window to find what you are actually searching with.

Also be aware that you query needs to be updated to search for text.  Since you were using numbers before, your query would have been something like:




```
Please Login or Register  to view this content.
```


but now it needs to be like this:




```
Please Login or Register  to view this content.
```


where the variable is surrounded by single quotes.

----------


## vampyr07au

> Can you try storing the entire search phrase as a variable so you can check the variable before running the query?  Something like this:
> 
> 
> 
> 
> ```
> Please Login or Register  to view this content.
> ```
> 
> ...



In relation to the below comment - wrapping it in the single quotes makes sense since that is what was reqd in the code found in the cmd button that controls the search however I do not understand where 



> SELECT * FROM tbl WHERE field1



 came from? 




> Also be aware that you query needs to be updated to search for text.  Since you were using numbers before, your query would have been something like:
> 
> 
> 
> 
> ```
> Please Login or Register  to view this content.
> ```
> 
> ...



The query is simply this:


```
Please Login or Register  to view this content.
```

----------


## davegugg

I just put a generic sql statement in.  



```
Please Login or Register  to view this content.
```


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

----------


## vampyr07au

> I just put a generic sql statement in.  
> 
> 
> 
> ```
> Please Login or Register  to view this content.
> ```
> 
> 
> 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 



```
Please Login or Register  to view this content.
```


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.

----------


## davegugg

When you go into the query editor, there should be a button on a toolbar that changes the query from design view to view to SQL view to datasheet view.  It wil probably look like a data table, and it will have a small black arrow pointing down next to it.  Press the black arrow, then click on the option that says SQL.  This will bring up the text of the SQL query.  Copy that and show us what it is.

----------


## vampyr07au

ok here ya go




```
Please Login or Register  to view this content.
```


That's the complete code via SQL view.

----------


## davegugg

Hmm, nothing jumps out at me.  Try this instead:




```
Please Login or Register  to view this content.
```


If that doesn't work, I'd try creating a new query now that your table structure has changed, and see if that gives you the results you are looking for.

----------


## vampyr07au

nope still no go ....  a partial search is giving me the no results msg box still .... full search is returning the out of memory runtime error. 

I am going to have a play with it over the next few days and will post back later.

----------

