以文本方式查看主题

-  Foxtable(狐表)  (http://foxtable.net/bbs/index.asp)
--  专家坐堂  (http://foxtable.net/bbs/list.asp?boardid=2)
----  多条件筛选  (http://foxtable.net/bbs/dispbbs.asp?boardid=2&id=139968)

--  作者:lxhmax
--  发布时间:2019/8/26 18:43:00
--  多条件筛选
老师,请问下这个条件的连接要怎么写,就是
1,0-90,110-120,140-160这个条件只要满足其中一个就可以,
2,10000-20000,50000-70000这个条件只要满足其中一个就可以
3,area1,area ,jg 最后这3个条件要同时满足

Dim Filter As String
Dim area1 As String = "广东省"
Dim area As String = "0-90,110-120,140-160" \'面积在0-90可以,或者110-120,或者140-160
Dim jg As String = "10000-20000,50000-70000"
Dim mjj As String
Dim jgg As String

If  area1 > "" Then
    If Filter > "" Then
        Filter = Filter & " Andalso "
    End If
    Filter = Filter & "一级区域 = \'" & area1 & "\'"
End If
If  area > "" Then
    If Filter > "" Then
        Filter = Filter & " Andalso "
    End If
    Dim Values() As String
    Values = area.split(",")
    For Index As Integer = 0 To Values.Length - 1
        Dim mjs() As String
        mjs = Values(Index).split("-")
        If mjj > "" Then
            mjj = mjj & " orelse " & "面积 >= \'" & mjs(0) & "\' Andalso 面积 <= \'" & mjs(1)
        Else
            mjj = "面积 >= \'" & mjs(0) & "\' Andalso 面积 <= \'" & mjs(1)
        End If
    Next
    Filter = Filter & mjj
End If
If  jg > "" Then
    If Filter > "" Then
        Filter = Filter & " Andalso "
    End If
    Dim Values() As String
    Values = jg.split(",")
    For Index As Integer = 0 To Values.Length - 1
        Dim mjs() As String
        mjs = Values(Index).split("-")
        If jgg > "" Then
            jgg = jgg & " orelse " & "价格 >= \'" & mjs(0) & "\' Andalso 价格 <= \'" & mjs(1)
        Else
            jgg = "价格 >= \'" & mjs(0) & "\' Andalso 价格 <= \'" & mjs(1)
        End If
    Next
    Filter = Filter & jgg
End If
Output.Show(filter)

--  作者:有点蓝
--  发布时间:2019/8/26 20:14:00
--  
Dim Filter As String
Dim area1 As String = "广东省"
Dim area As String = "0-90,110-120,140-160" \'面积在0-90可以,或者110-120,或者140-160
Dim jg As String = "10000-20000,50000-70000"

If  area1 > "" Then
    Filter = "一级区域 = \'" & area1 & "\'"
End If
If  area > "" Then
    If Filter > "" Then
        Filter = Filter & " And "
    End If
    Dim Values() As String
    Values = area.split(",")
    Dim f2 As String = " ( 1=2 "
    For Each s As String In Values
        f2 = f2 & " or 面积 BETWEEN " & s.Replace("-"," and ")
    Next
    Filter = Filter & f2 & " ) "
End If
If  jg > "" Then
    If Filter > "" Then
        Filter = Filter & " And "
    End If
    Dim Values() As String
    Values = jg.split(",")
    Dim f2 As String = " ( 1=2 "
    For Each s As String In Values
        f2 = f2 & " or 价格 BETWEEN " & s.Replace("-"," and ")
    Next
    Filter = Filter & f2 & " ) "
End If
Output.Show(filter)

--  作者:lxhmax
--  发布时间:2019/8/27 15:15:00
--  
谢谢老师,没看明白这个1=2是啥意思
--  作者:有点蓝
--  发布时间:2019/8/27 15:25:00
--  
用来凑条件,减少代码量
--  作者:lxhmax
--  发布时间:2019/8/27 15:50:00
--  
老师,有这个报错

图片点击可在新窗口打开查看此主题相关图片如下:微信截图_20190827154325.png
图片点击可在新窗口打开查看


Dim area As String = "0-90,110-120" \'面积
Dim area1 As String = "101" \'区域1
Dim area2 As String = "10101" \'区域2
Dim total As String = "0-90,110-120" \'价格
Dim shape As String = "0-1,1-2,2-3" \'房型
Dim houseage As String = "1-2" \'房龄
Dim Filter As String
If  area1 > "" Then
    Filter = "一级区域 = \'" & area1 & "\'"
End If
If  area2 > "" Then
    If Filter > "" Then
        Filter = Filter & " And "
    End If
        Filter = Filter & "二级区域 = \'" & area2 & "\'"
End If
If  area > "" Then
    If Filter > "" Then
        Filter = Filter & " And "
    End If
    Dim Values() As String
    Values = area.split(",")
    Dim f2 As String = " ( 1=2 "
    For Each s As String In Values
        f2 = f2 & " or 面积 BETWEEN " & s.Replace("-"," and ")
    Next
    Filter = Filter & f2 & " ) "
End If
If  total > "" Then
    If Filter > "" Then
        Filter = Filter & " And "
    End If
    Dim Values() As String
    Values = total.split(",")
    Dim f2 As String = " ( 1=2 "
    For Each s As String In Values
        f2 = f2 & " or 售价 BETWEEN " & s.Replace("-"," and ")
    Next
    Filter = Filter & f2 & " ) "
End If
If  shape > "" Then
    If Filter > "" Then
        Filter = Filter & " And "
    End If
    Dim Values() As String
    Values = shape.split(",")
    Dim f2 As String = " ( 1=2 "
    For Each s As String In Values
        f2 = f2 & " or 室 BETWEEN " & s.Replace("-"," and ")
    Next
    Filter = Filter & f2 & " ) "
End If
If  houseage > "" Then
    If Filter > "" Then
        Filter = Filter & " And "
    End If
    Dim Values() As String
    Values = houseage.split(",")
    Dim f2 As String = " ( 1=2 "
    For Each s As String In Values
        f2 = f2 & " or 房龄 BETWEEN " & s.Replace("-"," and ")
    Next
    Filter = Filter & f2 & " ) "
End If
output.Show(filter)
Tables("房产信息").Filter = Filter



--  作者:有点蓝
--  发布时间:2019/8/27 16:46:00
--  
output.Show(filter) 看看完整SQL
--  作者:lxhmax
--  发布时间:2019/8/27 16:51:00
--  
区域 = \'惠城\' And 片区 = \'东平\' And  ( 1=2  or 面积 BETWEEN 0 and 90 ) 

这样的报错

--  作者:有点蓝
--  发布时间:2019/8/27 17:00:00
--  
嗯,表达式不支持BETWEEN,比如这样

f2 = f2 & " or 面积 BETWEEN " & s.Replace("-"," and ")
改为
f2 = f2 & " or 面积 >= " & s.Replace("-"," and 面积 <= ")

其它地方参考自己改改