How to search for phrases in Bible Access database

I have the entire Bible stored in an Access database.  The name of the Bible books are store in text fields. The verses are stored in memo fields.  How can I quickly search all the verses for phrases and load that verse in the a richtextbox if it matches the query?

I was attempting to use the data control.
Who is Participating?
ysagada1012Connect With a Mentor Commented:

All you have to do is save the result of the query in a recordset then transfer all its contents to your richtextbox.


dim txtsql as string
dim txtverse as string
txtsql=SELECT Verse
FROM TableVerses
WHERE VerseText Like '*on the first day*'; (txtsql)

while not rs.eof
txtverse = txtverse & rs.fields(0).value

You could use the INSTR function.

For example:

if INSTR(1, verses.text, "garden") > 0 Then

the above searches the text field within the verses table for the word "garden".  If it is found, it returns the position of the word in the text.

jadedataMS Access Systems CreatorCommented:
Greetings mcdonald_g! and Experts

 This is a duplicate question.  Answers are also being taken on http:Q_21120467.html
 mcdonald_g:  Please request a delete of this question in Community Support ("Support" at the top of the page)

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

jadedataMS Access Systems CreatorCommented:
A quick re-read shows that the question are not exact duplicates.  

mcdonald_g:  You could probably have combined the questions considering they both relate to searching a specific table in your db project.

just a thought.
You can use the like operator in SQL to filter the results to only records containing a certain verse:

SELECT TableVerses.*
FROM TableVerses
WHERE TableVerses.VerseText Like '*on the first day*';

mcdonald_gAuthor Commented:
JR2003, can you provide all DB code for a data control do I can cut and paste?
A little more complete:
You will need to have a reference to "Microsoft ActiveX Data Objects 2.x Library" from the Project/References menu option.

Somewhere in a module you will have a conection object defined like this that will be connected to you database when you start your application, defined like this "Public g_MyDb As ADODB.Connection"

    Dim MySet As ADODB.Recordset
    Dim sSql As String
    sSql = "SELECT TableVerses.* "
    sSql = sSql & "FROM TableVerses "
    sSql = sSql & "WHERE TableVerses.VerseText Like '*on the first day*'"

    Set MySet = New ADODB.Recordset
    With MySet
        .Open sSql, g_MyDb
        While Not .EOF
            RichTextBox1.Text = RichTextBox1.Text & !VerseText
            'Add record processing code here
    End With
    Set MySet = Nothing

I have a similar case, but the verses are stored in RTF-encoded format. I can read the data as text, but it will not recognize the RTF codes, so I think I am pulling the data from Access as text rather than rtf.

I connect fine to the Access db. I can read any straight text verses fine, only bombs when reading RTF.

Can you help me?
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.