I have tried to use queries in the database to find words in multiple text fields contains many words

LibreOffice Version: (Database)
Build ID: 45e2de17089c24a1fa810c8f975a7171ba4cd432
Locale: en

Operating system is Mac OS X Yosemite version 10.10.2 in English


For interactive search to find the words “word” & “query”:

  1. Open the table
  2. Click the Find Record tool in Table Data toolbar
  • Search for Text: Word|Query
  • Where to search: All fields
  • Settings Position: anywhere in the field and check Regular expression

For exact search in a query you can use IN clause (see Query1 in QueryWords.odb)
For others (not exact search) you can use LOCATE (see Query2)

Nothing very “direct” sorry…

I would like to make a comment first. I use a Mac for all of my work. I have 2 computers. My new computer runs Yosemite; my old computer runs OS 10.5.8. Since I started using Macs instead of Windows, I have used AppleWorks as my office. I can’t use it on my new computer and I have no idea how long my old computer will continue to work, so I needed to find a replacement office program. After a lot of searching, I choose LibreOffice because it is the only office program on Mac that has all of the features that AppleWorks does.

There is one problem LibreOffice’s database is not as user friendly as AppleWorks is and LibreOffice lacks several very useful features that AppleWorks does and it is not nearly so intuitive. I looked for other possible databases for Mac, but none of them are as good as AppleWorks is. Please, could someone out there bring AppleWorks database back?

OK. Back to my original question. How do I find words in multiple text fields that contain many words? (Note: I am aware that most searches are case sensitive.)

I appreciate the answers I received. In fact, I had tried to use the IN clause in a Query without success. I would write an IN clause, such as IN(‘Missing’) and it would convert to ‘IN(’‘Missing’’)’. I didn’t realize that it was necessary to place a space between IN and (; once I did, the criterion remained IN (‘Missing’). However, I found out the following, unless Missing is the only word in the field searched, the search comes up empty. The field in question is the title of books. Thus if the title of the book is Crouching Buzzard, Leaping Loon, to find it with the IN command, I must write the query as IN (‘Crouching Buzzard, Leaping Loon’) in order to find this book. IN (‘Crouching Buzzard’) won’t work, nor will IN (‘Crouching Buzzard*’). So the IN command won’t help me find one work in a field of many.

I hadn’t heard of the LOCATE command and the example included with the answer showed my how to use it. This is a very complicated command, especially the need to include the criterion <> ‘0’ in order to insure that the result doesn’t include all of the records showing that they don’t include the word. However, if you search for the word ‘one’ in a Memo field, it will include one, done and any other word that contains one inside of it. This is also true if you search for ‘one’ in a field using the Find command.

I played around with the LIKE command in both Query mode and in Filter mode. It didn’t work at first because I tried LIKE ‘one’, for example, in my Memo field and no records were found. It took a while to discover that if ‘one’ was the first word in the memo field, than LIKE ‘one*’ would find me that record. If ‘one’ was a word in the middle of the memo field, than LIKE ‘one’ would find me that record, as well done, etc. If I only want to find the word ‘one’, then I must use the following LIKE command - LIKE ‘* one *’.

I hope this note helps other people out there.