IF ... THEN with SQL Yielding Syntax error 800A03EA

[vicshoup]

ASP & VB with MS Access. This recordset should spit out the results of a search from several select boxes on previous page.

I keep getting:

Error Type:
Microsoft VBScript compilation (0x800A03EA)
Syntax error
/RP_Ref_Guide/RP_search_results.asp, line 25
IF fRPname <> "" THEN results1=results1 &" WHERE tbl_Partners.Partner_company = " & fRPname & "" &_

If anyone can put me on course here, I'd be most grateful!

Here's the code:

<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
<%
Dim Repeat1__numRows
Dim Repeat1__index

Repeat1__numRows = -1
Repeat1__index = 0
results1_numRows = results1_numRows + Repeat1__numRows
%>
<!--#include file="Connections/RP_DB.asp" -->
<%
fRPname = Request.form("Partner_Name")
fRelname = Request.form("Relationship")
fIndname = Request.form("Industry")
fMgrname = Request.form("Partner_Manager")
fTername = Request.form("Territory")

Dim results1
Dim results1_cmd
Dim results1_numRows

Set results1_cmd = Server.CreateObject ("ADODB.Command")
results1_cmd.ActiveConnection = MM_RP_DB_STRING
results1_cmd.CommandText = "SELECT tbl_Partners.Partner_company, tbl_Relationship.relationship_name, tbl_Relationship.relationship_ID, tbl_Partners.relationship, tbl_Industry.industry_name, tbl_Industry.industry_ID, join_Industry_Partner.Industry_ID, tbl_Industry.Partner_ID, tbl_partners.Partner_ID, tbl_PartnerManager.partnerManager_name,  tbl_PartnerManager.partnerManager_ID, join_Partner_Manager.partnermanager_ID, join_Partner_Manager.partner_ID, territories.Terr_Name, territories.Terr_ID, join_territory_partner.Terr_ID  FROM tbl_Partners, tbl_Relationship, tbl_Industry, join_Industry_Partner, tbl_PartnerManager, join_Partner_Manager, territories, join_territory_partner " &_
			IF fRPname <> "" THEN results1=results1 &
			" WHERE tbl_Partners.Partner_company = " & fRPname & "" &_
			IF fRelname <> "" THEN results1 = results1 &
			" AND tbl_Relationship.relationship_name = " & fRelname & " AND tbl_Relationship.relationship_ID = tbl_Partners.relationship" &_
			IF fIndname <> "" THEN results1 = results1 &
			" AND tbl_Industry.industry_name = " & fIndname & "AND tbl_Industry.industry_ID = join_Industry_Partner.Industry_ID AND tbl_Industry.Partner_ID = tbl_partners.Partner_ID " &_
			IF fMgrname <> "" THEN results1 = results1 &
			" AND tbl_PartnerManager.partnerManager_name = " & fMgrname & " AND tbl_PartnerManager.partnerManager_ID = join_Partner_Manager.partnermanager_ID AND join_Partner_Manager.partner_ID = tbl_partners.Partner_ID " &_
			IF fTername <> "" THEN results1 = results1 &
			" AND territories.Terr_Name = " & fTername & " AND territories.Terr_ID = join_territory_partner.Terr_ID AND join_territory_partner.partner_ID = tbl_partners.Partner_ID ORDER by tbl_partners.Partner_company " &_
results1_cmd.Prepared = true

Set results1 = results1_cmd.Execute
results1_numRows = 0

			Set Partner = results("Partner_company")
			Set Manager = results("relationship_name")
			Set Territory = results("industry_name")
			Set Relationship = results("partnerManager_name")
			Set Industry = results("Terr_Name")
End if
Response.Write(results1)
%>

[Thirusha]

I dont use access but i think it has something to do with the your sql statement.
try writing the sql statment out.

just re-read your post, i was never liked to write vbscript with "&_" cause it always confused me, but since it is a compilation error, maybe u forgot an "&_" somewhere.

[vicshoup]

Thanks for taking a look. I scrapped the thing and started over, avoiding "&_".

Seemed promising but know I get "type mismatch" errors. I made a new post with this one.