ShinNathan — Tue, 22 May 2001 03:38:06 +0000

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 & "'"

dunno

1aspfan — Mon, 23 Apr 2001 19:10:23 +0000

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

Mark Hensler — Mon, 23 Apr 2001 19:00:24 +0000

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

Solution

1aspfan — Mon, 23 Apr 2001 14:03:51 +0000

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

Peter J. Boettcher — Mon, 23 Apr 2001 12:41:46 +0000

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:

sql = "SELECT Book, field, field "
sql = sql & "FROM bibletable "
sql = sql & "WHERE book = '" & book & "' "
sql = sql & "AND chapter = '" & chapter & "'" 

Just to verify line 32 is the sql right?

Mark Hensler — Mon, 23 Apr 2001 07:28:11 +0000

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.

cds — Mon, 23 Apr 2001 07:10:46 +0000

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.

Mark Hensler — Mon, 23 Apr 2001 05:35:38 +0000

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:

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

cds — Sun, 22 Apr 2001 13:27:19 +0000

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

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.

Mark Hensler — Sun, 22 Apr 2001 05:39:40 +0000

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.