Simple access query

ahh yes, access.... (blah)
It's just plain weird. And seems to never work the first time.

are your values integers? or strings?
try putting quotes around your values ("WHERE book='" & book & "' ...")
try taking the spaces out from between the field=value

I have no idea what it is, just some quick thoughts....

if thats your exact sql statement, try counting your quote marks. think it's a little short on the number needed.

nope... the number is fine.
You don't need a quote after chaper because its a variable, and not actually part of the SQL statement itself.

I don't see anything wrong with that SQL query. I'm sure that it works on other SQL servers. It's just that Access gets nit picky.

OK, let's break it down.
sql = "select * from bibletable Where book = " & book & " and chapter = "

I think it is running fine right up to that last quote and then it ends the sql statement and gives the error because it is looking for a value and it doesn't find it. Think if you moved the last " to after chapter, it would work.

I have one intranet website that is driven by access that has couple hundred statements just like this one and I can create and get the same error as 1aspfan is getting by removing the closing " mark.

no....

chapter is an ASP variable. It cannot go inside the quotes, or the value will not be inserted into the query. If your hard set about putting a quote after chapter, you will need to do it like this:

Mark, I apologize. Your right about the quotes and if that were the problem, you get a termination error or something similar, not the one he is getting.

I just had this error come up a couple days ago, but for the life of me, can't remember what caused it. Have even been trying to recreate the error in the script i was working on at the time and still can't get it. If I didn't want it, probably would be there every time.

Hopefully 1aspfan has figured it out and will share it with us. Has my curiosity up now.

No need to apologize. You just read it wrong

I've use Access as a backend once that I can remember. I have a hard time trouble shooting it. So I try to stay away from it.

Got in late on this one, try adding the single quotes as Mark said, and instead of using "SELECT *" do a "SELECT Book, field, field", there's an ADO bug that can fail on SELECT * (plus it's a good habit to get into anyways). I would write it as follows:

Just to verify line 32 is the sql right?

Alright guys, after beating my head against a wall for a few hours this is what I came up with:

sql = "select * from bibletable where book like" &" '%%" & u_input & "%%' " & _
"and chapter like " &" '%%" & chapter & "%%' " & "and verse like " &" '%%" & verse & "%%' " & "and result like " &" '%%" & keywords & "%%' "

Picky Picky Picky

It seems as though we are all very fond af access.
I appreciate all of the help!

Have a great day!

Mike

What is the purpose to the double percent signs (%%)?
Shouldn't you only need one wild card?

Mark,

I do not really know! I just know that it works. If someone could explain it I would like to know. I may try to take one of them out see if it still works. I will try that tonight and let you know. I am really liking SQL Server at this time.

Later
Mike

I think it is working now simply because it has single quotes now.

I am sure after you state equals (WHERE this =) you need to put the critera in quotes.

I think just this would work:

sql = "select * from bibletable Where book ='" & book & "' and chapter ='" & chapter & "'"