Ask Your Question

How to find specific words in a field query

asked 2017-11-24 02:17:46 +0100

this post is marked as community wiki

This post is a wiki. Anyone with karma >75 is welcome to improve it.


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.

edit retag flag offensive close merge delete


In the future, please do not check the Community Wiki box when asking questions. For more suggested guidelines, see

Jim K gravatar imageJim K ( 2017-11-24 18:04:38 +0100 )edit

2 Answers

Sort by » oldest newest most voted

answered 2017-11-24 17:59:05 +0100

Jim K gravatar image

updated 2017-11-24 18:14:34 +0100

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.

edit flag offensive delete link more


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. gravatar ( 2017-11-25 02:46:54 +0100 )edit

answered 2017-11-24 03:13:57 +0100

Ratslinger gravatar image


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:

image description

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:

image description

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 ✔ (upper left area of answer).

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2017-11-24 02:17:46 +0100

Seen: 342 times

Last updated: Nov 24 '17