How to find specific words in a field query


I have a table named John for my Bible Study Class. 6 Fields; Id, Testament, Book, Chapter, Verse, Passage.

I would like to be able to query the passage field so If I type in text like ’truth’ it will return the chapter, verse and passages that contain that word. My problem is that no matter what word I try under criterion for passages field, There are no records. Am I doing something wrong? I did query in design view and had the words I was looking for like ‘truth’ in single quotes next to criterion. No results. Everything else works for example if I put ‘28’ under criterion for the verse field, It returns all verse 28 chapters and passages. Thanks for help if possible.

In the future, please do not check the Community Wiki box when asking questions. For more suggested guidelines, see Guidelines for asking - #4 by jimk.


I believe your question is in regards to use the form filter. If so, start by selecting Filter Navigation icon giving you this screen:

image description

Then in the field you want to search, enter what you are looking for and press Enter:

image description

Then simply select the Apply Form-Based Filter icon for results:

All records containing that information in that field will be selected. This is case sensitive unless your table field is set as VARCHAR_IGNORECASE:

image description

Additionally, there are many ways to use an SQL query to return results. Here is just one:

The selection is done using a parameter for selection entry and ignores case (table setting doesn’t matter for this).

If this answers your question please click on the :heavy_check_mark: (upper left area of answer).

Set Criterion to LIKE '*truth*'.

query design view

Using LIKE '%truth%' also works, and seems better to me because it is like the underlying SQL. However, the query design form turns % into *.

Instead of modifying the query each time, use this Criterion to ask for the keyword to search.

LIKE LOWER ( '%' || :keyword || '%' )

Then enter truth in the Parameter Input box that appears.

Thank you for the advice. I did everything exactly like the screenshot. It worked. I did not try the parameter input box yet. Thanks again.