Ask Your Question

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

asked 2015-04-21 05:20:01 +0200

K0 gravatar image

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

Operating system is Mac OS X Yosemite version 10.10.2 in English

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted

answered 2015-04-21 15:04:12 +0200

pierre-yves samyn gravatar image


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... Regards

edit flag offensive delete link more

answered 2015-04-25 21:41:31 +0200

K0 gravatar image

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 ... (mehr)

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2015-04-21 05:20:01 +0200

Seen: 521 times

Last updated: Apr 25 '15