Search query problem - not returning a value

[aeaster]

The database is functioning OK (tables, forms and reports). I have one master table and 10 other tables that the master table uses for a lookup. The problem is with the search query that I have set up.



From the Query – Design I added the following string in the Criteria fields for each column:

LIKE “*” & [forms]![searchF]![Songs] & “*”

Where, search is the search form and Songs is the Column name.
When I run the query on the first column (Songs) it works perfectly, but when I run the query on any other column it does not return a value. I know that my criteria is set up properly, so I am assuming that the problem lies with the other tables being a lookup for the master table… maybe the relationships between all the tables??? Just don’t know. Any help?

Allyn

[rzw0wr]

If I am reading this correctly. You should not need your criteria in any column of the query but the song column.
You are telling the query to filter by Song.

Song is not in the other fields is it.?

Dale

[aeaster]

Dale... thanks for the reply. I want to be able to search by the other columns (composer, publisher, genre, theme, etc). I might search for a song by, say... composer = Bach and publisher = Rutter Music Productions. I want to display all songs that meet the criteria. BTW, the master table is called Songs.

Allyn

[rzw0wr]

OK. I think I understand.
If you put your filter criteria all on the same line the query looks at it as an "AND".
meaning that ALL criteria must be met.
Try putting each criteria on a separate line in your query.
The query then looks at it as an "OR" .
Meaning that if any one of the criteria is met filter by that.

Dale

[aeaster]

Thanks Dale... I gave it a try and still no returned values for criteria other than if I search on Song and no other column.

I appreciate your kind help... Allyn

[rzw0wr]

Strange.
I thought for sure that would do it.

I will give it some more thought.
Dale

Edit: You will only be able to use 1 criteria at a time this way.
Are you trying it with more than one?

[aeaster]

Yes... I need to search by more than one criteria. Here is a link to a You Tube tech article describing this search query.

http://www.youtube.com/watch?v=CTiA_4Me0cI

[rzw0wr]

I look at the Utube.
I see the problem.
Put all of the criteria back on the same line.
AND change the field name in each criteria to the field name of the query.

Like change song to composer for the composer field.

Dale

[aeaster]

Dale... thanks for the fix. My boss just dumped a project on my lap, so I won't be able to test until later this evening. I'll let you know. Thanks so much for your patience!

Allyn

[rzw0wr]

Glad to help.
Sorry I am so slow.
Still a newbie here.

Dale

[aeaster]

Well... the problem lies elsewhere. I have modified the criteria with the field name for each column. Using the search query on the primary column (Songs) works perfectly, but all other fields do not return a value. I followed the video carefully. I wonder if it has something to do with the relationships between the master table and the other tables.

Allyn

[rzw0wr]

One last thing.
Check to see that your text box names from the form are the names in the criteria in the query.


Dale

[aeaster]

yep! all good there. Guess I'll need to try to contact the author of the video for explanation.

Thanks,
Allyn

[rzw0wr]

There is data in the other fields, correct.
You have tried using just one of the other combo boxes by itself correct.

Strange
I will ponder some more.
If you did not have a 64 bit Access I would look at your database.
Dale

[rzw0wr]

OK, I re-read your posts.
I do not think your query is correct.
10 tables is a lot of tables to put into 1 query and have it work.
Take a very good look at your tables and make sure they are normalized well.

What I think may be happening is that the joins in your query are not correct to show the data you want.


Dale