Hello all,

I'm having a bit of a problem with a query that I am trying to run that searches a memo field, and I hope someone can help me!

Basically, I have a fairly simple database, 2 tables - 1 of the tables has a memo field which has 'keywords' listed in it (a mixture of words/phrases separated by commas, although I've tried with just spaces in between).

When I search using a Query box for 2 keywords using the following query, it can only find the correct records when I enter the keywords in the reverse order in which they appear in the memo field...
query: Like "*" & [Enter the 1st keyword to search for] And Like "*" & [Enter the 2nd keyword to search for] & "*"

If the memo field has data eg, "funding, teaching" (as opposed to "funding, nursing" for example) and I search for 'funding' and then 'teaching' - no results appear.
If however, I search for 'teaching' and then 'funding'... ta da! Result found.

Obviously, whoever searches the database isn't necessarily going to know what order the words were entered into the memo field!

So, is this a peculiarity to Access, or is there something I'm doing wrong?

Thank you in advance for your help.

change the AND operator to OR

Thanks for that - it works a treat...

Unfortunately, I should have clarified that I want both search terms to be 'true'... ie in my previous example it should only find records where the words 'funding' and 'teaching' appear (so as to differentiate from records where there may be 'funding' and 'nursing', or 'funding' and 'postgraduate study' for example).
I had tried the way with OR previously, but it brings up all records containing either 'funding' or 'teaching', when I only want it to bring up records with both.

So, it does seem odd that Access can't search a field for multiple items correctly... either that or I'm doing something horribly (and probably very simply) wrong!

If you want both to be true then AND it is. Try splitting your query into two with one the source of the other. So one query prompts for one parameter and the second query prompts for the other parameter.

Hoorah! Thanks Dennisk, that works fine! :)

Now, I thought the problems may have been possibly caused by my database structure, so I created another new, extremely simple, database with 2 fields - 1 an autonumber, the other the memo field, which I populated with different keywords.

I then set up my query as originally, with 2 prompts, separated by an AND clause... and again the correct records were only selected if I entered them into the prompts in the reverse order in which they appear in the memo field! :confused:

So, does anyone know why this happens? It seems a very strange behaviour for what I thought would be a fairly common sort of query...