专家坐堂-多条件筛选Foxtable(狐表) - 新一代数据库软件，完美融合Access、Foxpro、Excel、vb.net之优势，人人都能掌握的快速软件开发工具!

以文本方式查看主题

-  Foxtable(狐表)  (http://www.foxtable.com/bbs/index.asp)
--  专家坐堂  (http://www.foxtable.com/bbs/list.asp?boardid=2)
----  多条件筛选  (http://www.foxtable.com/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 面积 <= ")

其它地方参考自己改改