Help!! I need to make multiple fields searchable at one time. 1

[atadpole]

I have a similar problem to a previous post about making a field searchable. I used ZmrAbdulla's suggestion to create a a query based on the table and put a command in the criteria of the field I wanted to search that started with something like list (don't remember it now and it is on my other computer. Anyway, it partially fixed what I wanted to do, however it would only search on the one field. I would like to know if I can do the following and how would I take the suggestion ZmrAbdulla gave and maybe add to it? Here is what I have:
I have a table called Library Listings. In this table I have Call #, Author 1, Author 2, etc. all the way thru Author 6. I also have fields called Keyword 1, Keyword 2, etc. thru Keyword 4. I also have fields such as Title and Reference, but do not need to search on them. I currently have 2 buttons on the main switchboard, one to search by author and one by keyword. At this point I have it set up in the queries I built for each to search under Author 1 and Keyword 1, as suggested by ZmrAbdulla. I would like to find a way to search all 6 Author fields when I click on the button, same goes for the Keyword button. I am not up to speed on code building, so the easier the better. Thanks in advance for your help.

Andrea

 

[cghoga]

Build a query.
Include your table.
Include all your Author fields.
Build an expression.
In the field area type Authorvals: [Author1]&[[Author2] etc.
Do this until all author fields are included.
Then in the criteria section below, enter this.
Like "*" [Enter Search Criteria Here] & "*"
When this query is run it will prompt for criteria and should return all records where the string entered appears in the author fields.

 

[atadpole]

I did as suggested. The query still only searches on the Author 1 field. What is did was create a field called Authorvals that showed all the authors names together from the author fields.

My apologies if I have not been clear enough on what I am trying to do. Like I said, I am not an expert at this stuff. At any rate, what I want is to be able to press a button that will search all six author fields to then open a list of the papers that this author is named in whether he is in the Author 1 field or Author 6 field. Right now, the query only searches the Author 1 field.

I really appreciate your help.

Andrea

 

[cottontop]

cghoga answered it for you...

"In the field area type Authorvals: [Author1]&[[Author2] etc."

Expand it out...

Authorvals:[Author1]&[Author2]&[Author3]&[Author4]&[Author5]&[Author6]

You can also use the following. I find it works better:

Field = Authorval: instr([Author1]&[Author2]&[Author3]&[Author4]&[Author5]&[Author6],[Enter Search Info Here])
Criteria = >0

You can also do the following (only if the search string is going to be in one of the [author?] fields):

Field: Authorval: iif( instr([author1],[enter search info here]) >0, [author1],null) or iif( instr([author2],[enter search info here]) >0, [author2],null) or iif( instr([author3],[enter search info here]) >0, [author3],null) or
iif( instr([author4],[enter search info here]) >0, [author5],null) or iif( instr([author5],[enter search info here]) >0, [author5],null) or iif( instr([author6],[enter search info here]) >0, [author6],null)

However! You could put the last "Authorval" listed in a module to create your own function then use that function in the query. Again, that will only work if you know absolutely that only one of the 6 fields will contain what you're looking for.

Hope this helps!

 

[atadpole]

Thank you soooo much!!! I had to tweek it a little, but it works perfect. Now that I can search the way I want, I have one other question:

How do I create a way to make a message pop up that says something like "There is no record found that matches this criteria" when no match is found. Like if there is no author's name in the database that matches what they are searching for.

Thanks again for your help!!!!

 

[cottontop]

Glad we could help!

Field = Authorval: iif(instr([Author1]&[Author2]&[Author3]&[Author4]&[Author5]&[Author6],[Enter Search Info Here])>0, [Author1]&[Author2]&[Author3]&[Author4]&[Author5]&[Author6],"No Author Found!&quot

 

[GeekGirlau]

Andrea, I'd have a rethink about your data structure if I were you.

It sounds like you should have one table containing the book details (Title, Reference etc.) and a separate table containing the Authors. The link between the two needs to be a unique field from the book table - if Reference is unique to each book use this, otherwise create a new autonumber field in the book table.

The author table should use this unique index to indentify which authors relate to which book. For example,

tblLibraryBook
Title Reference
"Best Book Ever" "A12345"

tblAuthor
Reference Author
"A12345" "John Smith"
"A12345" "Jane Smith"

There are a number of books available on good database design - you might want to have a look at "Database Design for Mere Mortals" (can't remember the name of the author).