Hey guys,

I was hoping you can help me. I'm building a site for a real estate company. They want to search for houses for sale on a database. Multiple fields will be used in the search. The client may leave some fields empty during search.

This is the best I was able to come up with, but I get "Data type mismatch in criteria expression." error when the the fields are filled.

Dim PropertyCode, CityCode, StartPrice, EndPrice, MinBeds, MinBaths, MinBSF, MinAcres, YearBuilt
PropertyCode = Request("PropertyCode")
CityCode = Request("CityCode")
CityCode = Replace(CityCode, ", ", "','")
StartPrice = Request("StartPrice")
EndPrice = Request("EndPrice")
MinBeds = Request("MinBeds")
MinBaths = Request("MinBaths")
MinBSF = Request("MinBSF")
MinAcres = Request("MinAcres")
YearBuilt = Request("YearBuilt")

sql="SELECT * FROM Midfl_data WHERE PropertyCode = '" & PropertyCode & "' AND CityCode IN ('" & CityCode & "')"

If Request("StartPrice") = "0" Then
Else
sql = sql & "AND Price >= '" & StartPrice & "'"
End If

If Request("EndPrice") = "99999999999" Then
Else
sql = sql & "AND Price <= '" & EndPrice & "'"
End If

If Request("MinBeds") = "0" Then
Else
sql = sql & "AND Bedrooms >= '" & MinBeds & "'"
End If

If Request("MinBaths") = "0" Then
Else
sql = sql & "AND Bathrooms >= '" & MinBaths & "'"
End If

If Request("MinBSF") = "0" Then
Else
sql = sql & "AND BuildingSquareFeet >= '" & MinBSF & "'"
End If

If Request("MinAcres") = "0" Then
Else
sql = sql & "AND Acres >= '" & MinAcres & "'"
End If

If Request("YearBuilt") = "0" Then
Else
sql = sql & "AND YearBuilt >= '" & YearBuilt & "'"
End If

sql = sql & "Order by Price"

rs.open sql, oConn, 1, 1

P.S. I've found that if I insert the code for each field choice in the sql statement that the result page only pulls records that have data in these fields. Some records may have some fields empty. So if a customer leaves out a field I need it to pull record not just the ones with some data.

Thank you guys for any help you can provide.

You are inserting a value into a field that is the wrong data type or format. What DB are you using?

Next time you have a troubling SQL statement:

1..Mention the DB type
2..Coment out the execute part of code
3..Response.write the sql to the browser
4..Paste the query here (we want to see your values)
5..If its not to much trouble illustrate Data Types

If all this is one you will get a solution in one reply.

TIP 1 : Strip your sql back n run (insert one or two fields only) if it works the one or two you inserted are trouble free. Add another field n run, then another n run ...

Eventually your will find your problematic field

Tip 2: Get into the habbit of including spaces inside you conditional sql string, Eg:
sql = sql & "AND Price >= '" & StartPrice & "'"
Should be:
sql = sql & " AND Price >= '" & StartPrice & "'"

BTW: this is not your problem however a good habbit to get into

Tip 3: Post your sql related problems in the sql area of the forum.

Tip 4: Do you want to shorten your code? You dont need to:
PropertyCode = Request("PropertyCode")
CityCode = Request("CityCode")
...

Therefore you chnage:
sql="SELECT * FROM Midfl_data WHERE PropertyCode = '" & PropertyCode & "' AND

TO:
sql="SELECT * FROM Midfl_data WHERE PropertyCode = '" & Request("PropertyCode") & "' AND

I would also run trim functions around all your incoming values, Eg:
sql="SELECT * FROM Midfl_data WHERE PropertyCode = '" & trim(Request("PropertyCode")) & "' AND





Wind is your friend
Matt

Thanks Matt, I tried what you said and it worked.

Hello imercha.
I'm writing an aplication that enables to view and book flights between different cities.
And I have a problem.I don't know from where I can get the citycodes.
I read that you're also working with citycodes, so I decided to ask you to help me.
Please write anything you know.
Best Regards.