Pulling Results from Two Fields

[KellyHero]

Hi all:

I need some assistance please. I'm developing an ASP search page for a website and I'm having problems writing an SQL query statement that returns the results accurately.

Here is what I currently have:

MSQL = "SELECT * FROM properties WHERE act = 'Active' "

If Not Request.Querystring("s") = "" Then
qs = Split(Request.Querystring("s"), " ")
For i = 0 to UBound(qs)
 MSQL = MSQL & "AND pr_desc LIKE '%" & qs(i) & "%' "
Next
End If

If Not Request.Querystring("ag") = "" And Not Request.Querystring("ag") = "all" Then
ag = Request.Querystring("ag")
MSQL = MSQL & "AND pr_agent = " & ag & "  OR pr_agent2 = " & ag & " "
End If

If you search for a property only by keyword ("s&quot the results returned are accurate. If you search for a property only by agent1 or agent2, the results are accurate.

If you search for a property by keyword AND by agent, the results show properties which have either the keyword OR the agent is in agent2 field.

How can I combine the querystring so that it pulls only results that include the keyword AND the agent, whether the agent is agent1 or agent2?

 

[scriverb]

Have not built an ASP SQL string before but the logic seems to be obvious so I will give it a try:

MSQL = "SELECT * FROM properties WHERE act = 'Active' "

If Not Request.Querystring("s&quot = "" Then
qs = Split(Request.Querystring("s&quot, " &quot
For i = 0 to UBound(qs)
MSQL = MSQL & "AND pr_desc LIKE '%" & qs(i) & "%' "
Next
End If

If Not Request.Querystring("ag&quot = "" And Not Request.Querystring("ag&quot = "all" Then
ag = Request.Querystring("ag&quot
MSQL = MSQL & "AND (pr_agent = " & ag & " OR pr_agent2 = " & ag & " )"
End If

I included the red parens to group the agent expressions together in that it is an or expression. If either agent is selected it matches with the AND for the rest of the selection.

Post back with the results.


Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[KellyHero]

Worked perfectly! Thank you!

 

[scriverb]

Hey great. That was a little bit of a long shot but SQL is SQL with a little twist. You always have to be careful when you mis AND's and OR's.

Good luck with your project.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???