Query searching returns incorrect results

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

A couple of questions.

1. What was your data type before the change
2. what is your data type now
3. are you using a combo box to make your selection
4. if you are using a combo box, is the related lookup table linked to your query

Alan

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

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 ?

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

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.

Originally Posted by 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.

In relation to the above comment - I'm sorry but I do not understand what you mean by

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

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.

but now it needs to be like this:

Please Login or Register  to view this content.

where the variable is surrounded by single quotes.

The query is simply this:

Please Login or Register  to view this content.

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?

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.

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.