SQL "Between" (RESOLVED)

[epixelman]

What is wrong with this?

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

[epixelman]

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

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!

[epixelman]

Yes

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

and so is the mdb (Access97)

[epixelman]

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

[brucevde]

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

No idea why Revelations is being returned

[blindlizard]

Try it with these added parenthesis (sp?)

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

[epixelman]

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

[epixelman]

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??

[epixelman]

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....

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

[epixelman]

Well... solved that one too...

Grd1.Columns(4).Visible = False


Thanks