Multiple seach options 1

[donniea21]

I have a search page consisting of 5 options (3 dropdowns and 2 text fields)
strState = request.form("searchState&quot
strLOB = request.form("searchLOB&quot
strDep = request.form("searchDept&quot
strAreaofComp = request.form("txtAreaOfCompliance&quot
strRequirement = request.form("txtRequirement&quot
i am having trouble setting up the SQL statement so a user can search by one or many of these components. The three drops downs have various values including "ALL" if nothing else is specified. The user must specify at least one parameter

Thanks for any help

 

[pgferro]

I would try a dirty way :

strState = request.form("searchState")
strLOB = request.form("searchLOB")
strDep = request.form("searchDept")
strAreaofComp = request.form("txtAreaOfCompliance")
strRequirement = request.form("txtRequirement")

strSQL = "SELECT * FROM TableName WHERE "	
Number = 0
if strState <> &quot;ALL&quot; then
	strSQL = strSQL & &quot; STATE = '&quot;& strState &&quot;' &quot;
	Number = Number + 1
end if
if strLOB <> &quot;ALL&quot; then
	Number = Number + 1
	if Number > 1 then
		strSQL = &quot; AND &quot; & strSQL
	end if		
	strSQL = strSQL & &quot; LOB = '&quot;& strLOB &&quot;' &quot;
end if

'SAME FOR Dep, AreaofComp, Requirements

Let me know...

 

[Geee]

Whats so dirty about that? I use that method to construct all of my search pages =).

G -GTM Solutions, Home of USITE-
-=

http://www.gtmsolutions.com=-

 

[donniea21]

I think it will work...it seems to be going into the if strLOB <> all code though..So if i pick a state and leave LOB to all i get no results..If i pick a state and a LOB i get a result..Probably just a simple error...thanks for the help