I have two sql statements which I want to combine. One which searches the database for search terms and one which selects only entries where the parent ID column = "0" or the entered variable. I want the Sql to select only items which match both criteria (contain search terms AND parentid = "0"). The Sql is contained in a variable called"sqlString" which is defined early in the code. The parentid = "0" part is contained in the "rsmessages.source =" part. I want to set the variable "sqlString" to contain both criteria and then enter the variable "sqlString" into the "rsmessages.Source =". Below are the two Sql statments which I want to combine. Can someone show me how to do this, I continue to get syntax errors when I try to.
this is the variable where I want the entire sql to be stored, right now it contains only the search terms ("wordsArray" = the search terms):
sqlString = "SELECT * FROM forum2"
If UBound(wordsArray) > - 1 then
sqlString = sqlString & " WHERE "
For i = 0 to UBound(wordsArray)
sqlString = sqlString & " subject LIKE '%" & wordsArray(i) & "%' OR message LIKE '%" & wordsArray(i) & "%' OR"
sqlString = Left(sqlString, (Len(sqlString) - 3))
this is the sql which I would like to add into the varaible "sqlString" so that both criteria must be true in the database entries:
"SELECT * FROM forum2 WHERE parentID = " + Replace(rsmessages__MMColParam, "'", "''") + " ORDER BY ID DESC"
I hope this makes sense, if not please let me know.