Recordset \ SQL help

[CC801340]

I have a page which obtains a recordset based on the users choice from
2 list boxes on the previous page and then displays the record set.

The user selects a jobtype and location - the SQL to retrieve the
recordset is:

SELECT SpecNo, Salary, JobType, Location, JobTitle, JobID
FROM Tbl_Jobs
WHERE JobType = 'MMColParam' AND location = 'MMColParam1'
ORDER BY SpecNo DESC

The two parameters are simple Request("xxxx&quot statements.

However, I have another page which only gives the user the option to
search by jobtype. When they submit - there is no location passed to
the search page. How can I search for all locations?

Many thanks

 

[Cheech]

put

IF Request(&quot;location&quot <> &quot;&quot; then MMColParam1 = &quot;%&quot; End IF

where &quot;%&quot; = the default value for your database(that one is access) Give it a go A child of five would understand this. Send someone to fetch a child of five.
Groucho Marx (1895-1977)

 

[CC801340]

I want the SQL statement to read something like this:

SELECT SpecNo, Salary, JobType, Location, JobTitle, JobID
FROM Tbl_Jobs
WHERE JobType = 'MMColParam' AND location = &quot;1&quot; OR &quot;2&quot; OR &quot;3&quot; OR &quot;4&quot; OR &quot;5&quot; OR &quot;6&quot; ORDER BY SpecNo DESC

How can i therefore make the &quot;%&quot; something like 1&quot; OR &quot;2&quot; OR &quot;3&quot; OR &quot;4&quot; OR &quot;5&quot; OR &quot;6&quot;?

Thanks

 

[Cheech]

SELECT SpecNo, Salary, JobType, Location, JobTitle, JobID
FROM Tbl_Jobs
WHERE JobType = 'MMColParam' AND location LIKE &quot;%&quot; A child of five would understand this. Send someone to fetch a child of five.
Groucho Marx (1895-1977)

 

[CC801340]

Ive changed the code so it reads:

<%
Dim JS__MMColParam1
JS__MMColParam1 = &quot;1&quot;
set JS = Server.CreateObject(&quot;ADODB.Recordset&quot
JS.ActiveConnection = MM_hotjobs_STRING
If (Request(&quot;location&quot <> &quot;&quot then JS.Source = &quot;SELECT SpecNo, Salary, JobType, Location, JobTitle, JobID FROM Tbl_Jobs WHERE JobType = '&quot; + Replace(JS__MMColParam, &quot;'&quot;, &quot;''&quot + &quot;' ORDER BY SpecNo DESC&quot;
else
JS.Source = &quot;SELECT SpecNo, Salary, JobType, Location, JobTitle, JobID FROM Tbl_Jobs WHERE JobType = '&quot; + Replace(JS__MMColParam, &quot;'&quot;, &quot;''&quot + &quot;' AND location = '&quot; + Replace(JS__MMColParam1, &quot;'&quot;, &quot;''&quot + &quot;' ORDER BY SpecNo DESC&quot;
end if

Im using VBScript but Im getting a syntax error with the last line!!? Any ideas!?

 

[mkistler]

I'm not sure, but I suspect that the problem is that there are double-quotes meaning different things in the statement and the syntax checker is getting confused. Try using the replace functions on local variables before setting up the select statement. Then use the new variables to build the select statement.

temp1 = Replace(JS__MMColParam, &quot;'&quot;, &quot;''&quot
temp2 = Replace(JS__MMColParam1, &quot;'&quot;, &quot;''&quot

If (Request(&quot;location&quot <> &quot;&quot then JS.Source = &quot;SELECT SpecNo, Salary, JobType, Location, JobTitle, JobID FROM Tbl_Jobs WHERE JobType = '&quot; + temp1 + &quot;' ORDER BY SpecNo DESC&quot;
else
JS.Source = &quot;SELECT SpecNo, Salary, JobType, Location, JobTitle, JobID FROM Tbl_Jobs WHERE JobType = '&quot; + temp1 + &quot;' AND location = '&quot; + temp2 + &quot;' ORDER BY SpecNo DESC&quot;
end if