SQL to filter by entry "Status"

[HappyChapZA]

Hi all,

I have the following code which displayes properties to let when searched for by "City/Town", "Suburb", "Type" and then between two "Rental" amounts. I would like to also include filtering by the "Status" field so that only approved entries will be displayed in a search result. Any help will be much appreciated.

Here is the code:

<%
Dim Recordset1__varRental1
Recordset1__varRental1 = "0"
If (Request.Querystring("Rental1") <> "") Then 
Recordset1__varRental1 = Request.Querystring("Rental1")
End If
%>
<%
Dim Recordset1__varRental2
Recordset1__varRental2 = "0"
If (Request.Querystring("Rental2") <> "") Then 
Recordset1__varRental2 = Request.Querystring("Rental2")
End If
%>
<%
Dim Recordset1__varSuburb
Recordset1__varSuburb = "%"
If (Request.Querystring("Suburb") <> "") Then 
Recordset1__varSuburb = Request.Querystring("Suburb")
End If
%>
<%
Dim Recordset1__varType
Recordset1__varType = "%"
If (Request.Querystring("Type") <> "") Then 
Recordset1__varType = Request.Querystring("Type")
End If
%>
<%
Dim Recordset1__varCityTown
Recordset1__varCityTown = "%"
If (Request.Querystring("CityTown") <> "") Then 
Recordset1__varCityTown = Request.Querystring("CityTown")
End If
%>
 
<%
Dim Recordset1
Dim Recordset1_numRows
 
Set Recordset1 = Server.CreateObject("ADODB.Recordset")
Recordset1.ActiveConnection = MM_Tolet_STRING
Recordset1.Source = "SELECT UserID, Photo, Status, When, Owner, CityTown, Suburb, Type, Bedrooms, Bathrooms, Available, Ownerphone, Rental, Description FROM Table1 WHERE Rental BETWEEN " + Replace(Recordset1__varRental1, "'", "''") + " AND " + Replace(Recordset1__varRental2, "'", "''") + " AND Suburb LIKE '" + Replace(Recordset1__varSuburb, "'", "''") + "' AND Type = '" + Replace(Recordset1__varType, "'", "''") + "' AND CityTown = '" + Replace(Recordset1__varCityTown, "'", "''") + "'"
Recordset1.CursorType = 0
Recordset1.CursorLocation = 2
Recordset1.LockType = 1
Recordset1.Open()
 
Recordset1_numRows = 0
%>

[jhardman]

The important part here is the line which says Recordset1.source = ...
This line is sometimes called the database query, it just says which fields you are interested in, and which records apply to the current search. Notice your line is already pretty long and complicated. We can simplify it a bit especially in the first line like this, "SELECT * FROM Table1 ..." You originally had listed every field which you wanted the db to give you, my version just says "Give me every field from the records I want."
The last part of your query is the "WHERE clause" which lists the conditions for the records you want. The syntax for this clause should be

"WHERE rental BETWEEN 300 AND 500 AND suburb LIKE 'sleazy' AND type = 'studio' AND status = 0"

Notice that each condition is separated by the keyword "AND", words or frases are included in single quotes, but numbers are not. I am guessing that you have a numeric status, in which case you need to add

"AND status = " & theStatus

to the end of the where clause. If your status is a text, then it needs to go like this:

"AND status = '" & theStatus & "'"

Let me know if this helps, and if so, give me a break next time I need to look for a house.

Jared