SQL "Between" (RESOLVED)

What is wrong with this?

VB Code:

SearchData.Open "SELECT Book,Chapter,Verse,Scripture From Bible " & _
                "Where Book ='" & Combo1.List(Combo1.ListIndex) & "' BETWEEN '" & Combo2.List(Combo2.ListIndex) & "' " & _
                "AND Scripture LIKE '%" & Text1.Text & "%'", cnn

I return no records using the BETWEEN.

I know the rest already works because I tested it. When I added BEWTEEN it returns zero records.

I NEED HELP FAST... deadline

Okay... I got it to at least display records. I had the BETWEEN in wrong place...

VB Code:

SearchData.Open "SELECT Book,Chapter,Verse,Scripture From Bible " & _
                "Where Book BETWEEN '" & Combo1.List(Combo1.ListIndex) & "' AND '" & Combo2.List(Combo2.ListIndex) & "' " & _
                "AND Scripture LIKE '%" & Text1.Text & "%'", cnn

But now... it doesn't select the records bewteen say...

Genesis to Joshua.

I am getting all the way to Revelations.

Please HElp!

Yes

Each combo is in order of the KJV bible form Genesis to Rev

and so is the mdb (Access97)

Hey.... where did brucevde's post go??

Sorry, I deleted it, as you had already solved your original question.

No idea why Revelations is being returned

Try it with these added parenthesis (sp?)

Code:

---

SearchData.Open "SELECT Book,Chapter,Verse,Scripture From Bible " & _
                "Where Book BETWEEN ('" & Combo1.List(Combo1.ListIndex) & "' AND '" & Combo2.List(Combo2.ListIndex) & "' " & _
                ") AND Scripture LIKE '%" & Text1.Text & "%'", cnn

---

() returns a "data type mismatch in criteria expression error."

I figured something out here....

When I select "Genesis" (combo1) through "Joshua" (combo2)

It only returns records that start with the book names from G to J

I tried it on Ruth to Zechariah and I only got records that are from R or Z.


What is with the "first letter criteria"???

Somhow I got to let my recordset know that we want to look from Genesis to Revelations and include all books (verses) that start with other letters beside G to R and without leaving out A-F and S-Z

Any ideas??

ALRIGHT!!!

I figured it out!

I have a "BookID" that numbers each book..... "Genesis = 1 and Revelation = 66".... okay.

So, I did the BETWEEN with the "BookId" instead of "Book"

Instead of using the text in the combo boxes, I used the combos listindex + 1 , which refers to the "BOOKID" in the mdb.

Here it is....

VB Code:

SearchData.Open "SELECT Book,Chapter,Verse,Scripture,BookID From Bible " & _
                "Where BookID BETWEEN " & Combo1.ListIndex + 1 & " AND " & Combo2.ListIndex + 1 & " " & _
                "AND Scripture LIKE '%" & Text1.Text & "%'", cnn

Thanks so far!



==============================
NEXT QUESTION


How do I hide my "BookID" column in my datagrid. It dislays when I send the recordset to the datagrid?

Thanks

Well... solved that one too...

Grd1.Columns(4).Visible = False


Thanks