search problem

[laxfan]

I am having a problem with my search. It only seems to search through XREF. It won't search through Site3, Site 28 or Site 29. Does anyone know why?

' generate SQL statement based on Site ID
If varSiteID = 8 Then

strSQLKeywords= "SELECT XrefMemos.MemoNumber, XrefMemos.MemoTitle, XrefMemos.MemoDate, " & _
"XrefMemos.IndexNumber " & _
"FROM XrefMemos INNER JOIN Subjects ON " & _
"XrefMemos.IndexNumber = Subjects.IndexNumber " & _
"WHERE XrefMemos.MemoKeywords LIKE '% " & varLCKeywords & "%' " & _
"OR XrefMemos.MemoTitle LIKE '%" & Trim(varUCKeywords) & "%' " & _
"OR Subjects.SubjectName LIKE '%" & Trim(varUCKeywords) & "%' " & _
"GROUP BY XrefMemos.MemoNumber, XrefMemos.MemoTitle, XrefMemos.MemoDate, " & _
"XrefMemos.IndexNumber " & _
"ORDER BY XrefMemos.MemoDate DESC;"

ElseIf varSiteID = 3 OR varSiteID = 28 OR varSiteID = 29 Then

strSQLKeywords= "SELECT Subjects.SubjectID, Subjects.SubjectName, " & _
"Content.ContentDate " & _
"FROM Subjects, Content " & _
"WHERE Subjects.PageID = " & varPageID & " " & _
"AND Content.SubjectID = Subjects.SubjectID " & _
"AND Content.Keywords LIKE '% " & varLCKeywords & "%' " & _
"ORDER BY Content.ContentDate DESC;"

 

[dbomrrsm]

what error do you get ?

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[laxfan]

There is no error message. It just comes up with the statement 'no documents where found using these keywords'. I've added all the keywords into the database but it still won't work.

 

[Catadmin]

Stupid thought, but sometimes it helps me. Have you tried adding Parens ( ) around the outsides of your Then statements and your Else If statements?

It might help. Your code might be dropping out at the first part of the If/Then string as resolved because it doesn't realize the whole Else statement goes together or the whole Then groups are together.

Also, I don't see a space between your Else and your If. Don't know if that was a typo or if that was on purpose.



Catadmin - MCDBA, MCSA
Remember, "Work" is a four letter word. And you know what your mother told you about using four letter words!

 

[laxfan]

Thanks for your help. I was wondering, though, if you could be a little more explicit. Could you show me exactly where you think the parens should go (in the coding from the previous e-mai) using a different colour so I can see. thanks a million for your help!

 

[JamesLean]

I don't think the SQL is your problem here. Are you saying it only ever executes the first SQL statement, not the second? If so, then it must be a problem with your VB code (specifically how are you getting the value for varSiteID and/or why is it always 8?).

In any case you should probably post this question in a forum relating to your coding language.

--James