SQL statement for property search

[FlorisKruger]

HELP!! I am very new to this and very much lost! What I need to achieve is a property search which lists properties with the search criteria as shown but prices are selected from "from" and "to" fields ranging between 100 000 and 3 000 000.

SELECT Type, CityTown, Suburb, Price, Bedrooms, Bathrooms, EnSuite
FROM Main
WHERE Price BETWEEN varPrice1 AND varPrice2 AND Suburb = 'varSuburb' AND Type = 'varType'
ORDER BY Price

varSuburb % Request.Form("Suburb&quot
varType % Request.Form("Type&quot
varPrice1 0 Request.Form("Price1&quot
varPrice2 0 Request.Form("Price2&quot

 

[JimYellow]

here is an example of how we build sql strings
and set the results in a listbox (its the same for a sub form control sourse)

Private Sub SetSearchResults()

Dim strSQL As String
strSQL = ""

strSQL = "SELECT * FROM SurveyDetails WHERE ((JobID) =" & dlgSurvey.JobID & &quot"
Select Case SearchBetween
Case 1 'between dates
If Not isblank(Date1) Then
If Not isblank(Date2) Then
strSQL = strSQL & " AND (SurveyDetails.ActualDateOfSurvey between #" & Format(Me.Date1, "mm dd yyyy&quot & "# AND #" & Format(Me.Date2, "mm dd yyyy&quot & "#)"
Else
MsgBox "You must enter two dates to search between"
End If
Else
MsgBox "You must enter two dates to search between"
End If
Case 2 ' single date
If Not isblank(Me.Date1) Then
strSQL = strSQL & " AND SurveyDetails.ActualDateOfSurvey = #" & Format(Me.Date1, "mm dd yyyy&quot & "#"
Else
MsgBox "You must enter a date to search upon"
End If
Case Else ' not selected option
MsgBox "You must choose how you wish to search for surveys"
End Select

strSQL = strSQL & ";"
SearchResults.RowSource = strSQL
Debug.Print strSQL
SearchResults.Requery
End Sub

 

[FlorisKruger]

Thank you kindly but I see and I don't see! Can you not take my effort and put that into laymans terms so that I may make some sense of it? Forgive me but I am by no means a programmer.

 

[JimYellow]

create a form with

Fields (Suburb, xType, Price1, Price2)
the fields can be combos, listbox or text box

add a listbox called SearchResults configure the columns etc

add a comand button called search which calls the procedure below
or call it from afterupdate events

Private Sub SetSearchResults()

Dim strSQL As String
Dim haswhere As Boolean
haswhere = False
strSQL = ""

strSQL = "SELECT Type, CityTown, Suburb, Price, Bedrooms, Bathrooms, EnSuite "
strSQL = strSQL & "FROM Main "

If isnull(Me.Suburb) Then
Else
If haswhere Then
strSQL = strSQL & " AND (([Main].[Suburb]) = '" & Me.Suburb & "')"
Else
strSQL = strSQL & " WHERE ((([Main].[Suburb]) = '" & Me.Suburb & "')"
haswhere = True
End If
End If

If isnull(Me.xType) Then
Else
If haswhere Then
strSQL = strSQL & " AND (([Main].[xType]) = '" & Me.xType & "')"
Else
strSQL = strSQL & " WHERE ((([Main].[xType]) = '" & Me.xType & "')"
haswhere = True
End If
End If

If isnull(Me.xType) Then
Else
If haswhere Then
strSQL = strSQL & " AND (([Main].[Price]) BETWEEN " & Me.Price1 & " AND " & Me.Price2 & &quot"
Else
strSQL = strSQL & " WHERE ((([Main].[Price]) = " & Me.Price1 & " AND " & Me.Price2 & &quot"
haswhere = True
End If
End If


If haswhere Then
strSQL = strSQL & &quot"
Else
End If
strSQL = strSQL & ";"
'print the sql to the imediate window for copying to the query builder
Debug.Print strSQL
'Change the list in the combo or list box
Me.SearchResults.RowSource = strSQL
Me.SearchResults.Requery
End Sub

 

[FlorisKruger]

Thank you very much! I shall give this my best shot but if to no avail I shall have to bother you once more. I am from the dark continent (the continent, not me) of Africa and your kind response is much appreciated.