Foxtable(狐表)用户栏目专家坐堂 → 多条件筛选


  共有5285人关注过本帖树形打印复制链接

主题:多条件筛选

帅哥哟,离线,有人找我吗?
lxhmax
  1楼 | QQ | 信息 | 搜索 | 邮箱 | 主页 | UC


加好友 发短信
等级:三尾狐 帖子:637 积分:5704 威望:0 精华:0 注册:2012/8/2 19:04:00
多条件筛选  发帖心情 Post By: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)

 回到顶部
帅哥哟,离线,有人找我吗?
有点蓝
  2楼 | 信息 | 搜索 | 邮箱 | 主页 | UC


加好友 发短信
等级:超级版主 帖子:111381 积分:566969 威望:0 精华:9 注册:2015/6/24 9:21:00
  发帖心情 Post By: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
  3楼 | QQ | 信息 | 搜索 | 邮箱 | 主页 | UC


加好友 发短信
等级:三尾狐 帖子:637 积分:5704 威望:0 精华:0 注册:2012/8/2 19:04:00
  发帖心情 Post By:2019/8/27 15:15:00 [只看该作者]

谢谢老师,没看明白这个1=2是啥意思

 回到顶部
帅哥哟,离线,有人找我吗?
有点蓝
  4楼 | 信息 | 搜索 | 邮箱 | 主页 | UC


加好友 发短信
等级:超级版主 帖子:111381 积分:566969 威望:0 精华:9 注册:2015/6/24 9:21:00
  发帖心情 Post By:2019/8/27 15:25:00 [只看该作者]

用来凑条件,减少代码量

 回到顶部
帅哥哟,离线,有人找我吗?
lxhmax
  5楼 | QQ | 信息 | 搜索 | 邮箱 | 主页 | UC


加好友 发短信
等级:三尾狐 帖子:637 积分:5704 威望:0 精华:0 注册:2012/8/2 19:04:00
  发帖心情 Post By: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



 回到顶部
帅哥哟,离线,有人找我吗?
有点蓝
  6楼 | 信息 | 搜索 | 邮箱 | 主页 | UC


加好友 发短信
等级:超级版主 帖子:111381 积分:566969 威望:0 精华:9 注册:2015/6/24 9:21:00
  发帖心情 Post By:2019/8/27 16:46:00 [只看该作者]

output.Show(filter) 看看完整SQL

 回到顶部
帅哥哟,离线,有人找我吗?
lxhmax
  7楼 | QQ | 信息 | 搜索 | 邮箱 | 主页 | UC


加好友 发短信
等级:三尾狐 帖子:637 积分:5704 威望:0 精华:0 注册:2012/8/2 19:04:00
  发帖心情 Post By:2019/8/27 16:51:00 [只看该作者]

区域 = '惠城' And 片区 = '东平' And  ( 1=2  or 面积 BETWEEN 0 and 90 ) 

这样的报错

 回到顶部
帅哥哟,离线,有人找我吗?
有点蓝
  8楼 | 信息 | 搜索 | 邮箱 | 主页 | UC


加好友 发短信
等级:超级版主 帖子:111381 积分:566969 威望:0 精华:9 注册:2015/6/24 9:21:00
  发帖心情 Post By:2019/8/27 17:00:00 [只看该作者]

嗯,表达式不支持BETWEEN,比如这样

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

其它地方参考自己改改


 回到顶部