Concatenate function

[BrianPaul]

This may be tough to do. Need suggestions. I have several Acces Bible
Databases. Of course each verse is contained into one record that means over
31 thousand records or verses. Like to combine them. I know I can do that in
a report, However; trying to develope search routines, etc. Or another
solution which I have no idea is possible, is to display a number of records
before or after the word search. Such as the entire book and chapter. Any
Ideas would be greatly appreciated. Thanks

 

[KARL DEWEY]

display a number of records before or after the word search. Such as the
entire book and chapter.
Hopefully you setup a primary key using an autonumber. First verify that
the autonumber has no blanks then when you find word/phrase just have query
return records +/- from the number where word/phrase was found.

If you add field for Book and Chapter you can pull based on that.

 

[BrianPaul]

Chu ching, Karl, I did think of that. Yes each record has a primary key and
are autonumbered.

Like to do it with entire book. I have used the like statements in queries
and even the highlighted search form. However, never used the syntax to
return so many records before or after.

 

[John W. Vinson]

Like to do it with entire book. I have used the like statements in queries
and even the highlighted search form. However, never used the syntax to
return so many records before or after.

Assuming that your table has fields for Book and Chapter, you could use a form
to return the individual verse, the chapter, or the entire book based on the
results of a search. What is the actual structure (tablenames and fieldnames)
of your table?

And have you considered using one of the many free or inexpensive Bible
databases? I am pretty good at Access, but I'll still pop up Quickverse when I
need this kind of functionality!

 

[Allen Browne]

Presumably you have set up:
a) a table of books (BookNum as primary key, records 1 - 66.)
b) a table of BookChapters (BookNum + ChapterNum as p.k.)
c) a table of verses, with fields:
o VerseID Number. P.K. (Must be sequential, preferably
contiguous)
o BookNum Number
o ChapterNum Number
o VerseNum Number
o VerseText Memo
All fields are required, and there's a unique index on the combination of
BookID + ChapterID + VerseID.

That arrangement lets you specify a range by:
- start of range: accept a book [and optionally with chapter [and optionally
with verse]], and look up the VerseID.
- end of range: same.
Now your query criteria is merely a simple expression such as:
WHERE VerseID Between 734 And 1234
and this works across chapter or even book boundaries.

Normally you could output that into a form or report without having to
concatenate the text. If you really need to concatenate into a single
string, you could code a function to loop through the records in the range
and do so. It would be something like this:
http://allenbrowne.com/func-concat.html

Having said that, I question whether this is really worth the effort in
Access when there are so many brilliant Bible software packages on the
market. The example above:
- doesn't handle different translations.
- doesn't handle textual variations.
- doesn't cope with other canons (e.g. apocrypha in Catholic Bible)
- doesn't handle original language study
- doesn't cope with different numberings (e.g. the chapter and verse numbers
in Psalms in the Hebrew are different)
- doesn't handle proximity searches (e.g. X within 4 words of Y.)
and so on,.

I suspect you'd be much better off to buy an existing product such as Logos:
logos.com.
or a freebie such as E-Sword:
http://e-sword.net/downloads.html
or on-line Bible:
http://www.onlinebible.org/html/eng/starterspack.htm

Unless you are doing something really weird, Logos would do it better and
much more efficiently than Access. Compare the price of (say) Logos Bible
Study Library against whatever you think your time is worth to do it in
Access.