SQL Select Help Please
Hi
I'm struggling with this sql statement and hope someone can help me out.
Im running dw 8 in Classic ASP and Access DB
I have a search page and a results page.
My search page has several dropdown menus that pass values to my results
page.
I want my results page to display results for ANY of the values passed from
my search page and not depend on all values.
So if a visitor only makes selections from two or three dropdown options, i
get results based on those selections.
At present im thinking that my results page needs all values as i use the
AND in my select statement. Is this correct? if so how do i achieve my
goal??
Thank You
Andy
I have my results page sql select code below:
<%
Dim RSResults__MMColParam2
RSResults__MMColParam2 = "0"
If (Request("location") <> "") Then
RSResults__MMColParam2 = Request("location")
End If
%>
<%
Dim RSResults__MMColParam3
RSResults__MMColParam3 = "0"
If (Request("Instrument") <> "") Then
RSResults__MMColParam3 = Request("Instrument")
End If
%>
<%
Dim RSResults__MMColParam4
RSResults__MMColParam4 = "0"
If (Request("Person") <> "") Then
RSResults__MMColParam4 = Request("Person")
End If
%>
<%
Dim RSResults__MMColParam5
RSResults__MMColParam5 = "0"
If (Request("AssetID") <> "") Then
RSResults__MMColParam5 = Request("AssetID")
End If
%>
<%
Dim RSResults__MMColParam6
RSResults__MMColParam6 = "0"
If (Request("Description") <> "") Then
RSResults__MMColParam6 = Request("Description")
End If
%>
<%
Dim RSResults__MMColParam7
RSResults__MMColParam7 = "0"
If (Request("TestStatus") <> "") Then
RSResults__MMColParam7 = Request("TestStatus")
End If
%>
<%
Dim RSResults__MMColParam8
RSResults__MMColParam8 = "0"
If (Request("TestType") <> "") Then
RSResults__MMColParam8 = Request("TestType")
End If
%>
<%
Dim RSResults__MMColParam9
RSResults__MMColParam9 = "0"
If (Request("FromDate") <> "") Then
RSResults__MMColParam9 = Request("FromDate")
End If
%>
<%
Dim RSResults__MMColParam10
RSResults__MMColParam10 = "0"
If (Request("ToDate") <> "") Then
RSResults__MMColParam10 = Request("ToDate")
End If
%>
<%
Dim RSResults
Dim RSResults_numRows
Set RSResults = Server.CreateObject("ADODB.Recordset")
RSResults.ActiveConnection = MM_calcert_STRING
RSResults.Source = "SELECT DISTINCT Assets.[Asset ID], Assets.Description,
Locations.[Location Name], [Asset Tests].[Test Date], [Asset Tests].[Test
Instrument Serial No], [Asset Tests].User, [Asset Test Records].[Test Type],
[Asset Test Records].Value, [Asset Test Records].Unit, [Asset Test
Records].[Passed Test] FROM Locations INNER JOIN ((Assets INNER JOIN [Asset
Tests] ON Assets.[Asset Key] = [Asset Tests].[Asset ID]) INNER JOIN [Asset
Test Records] ON [Asset Tests].[Test ID] = [Asset Test Records].[Test ID])
ON Locations.ID = Assets.Location WHERE Locations.[Location Name] = '" +
Replace(RSResults__MMColParam2, "'", "''") + "' AND [Asset Tests].[Test
Instrument Serial No] = '" + Replace(RSResults__MMColParam3, "'", "''") + "'
AND [Asset Tests].User = '" + Replace(RSResults__MMColParam4, "'", "''") +
"' AND Assets.[Asset ID] = '" + Replace(RSResults__MMColParam5, "'", "''")
+ "' AND Assets.Description = " + Replace(RSResults__MMColParam6, "'",
"''") + " AND [Asset Test Records].[Passed Test] = '" +
Replace(RSResults__MMColParam7, "'", "''") + "' AND [Asset Test
Records].[Test Type] = " + Replace(RSResults__MMColParam8, "'", "''") + "
AND [Asset Tests].[Test Date] BETWEEN '" + Replace(RSResults__MMColParam9,
"'", "''") + "' AND '" + Replace(RSResults__MMColParam10, "'", "''") + "'
ORDER BY Assets.[Asset ID] ASC"
RSResults.CursorType = 0
RSResults.CursorLocation = 2
RSResults.LockType = 1
RSResults.Open()
RSResults_numRows = 0
%>
|
