专家坐堂-[求助]关于空值控件查询问题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=93749)

--  作者：悠悠yo
--  发布时间：2016/12/6 11:12:00
--  [求助]关于空值控件查询问题

此主题相关图片如下：qq截图20161206111018.png

如图所示三个控件,要用这三个控件的值查询.但是也有为空时候,因为空值查不到值,所以要判断其中一个为空,两个为空,三个为空的各种情况

--  作者：悠悠yo
--  发布时间：2016/12/6 11:19:00
--

Dim tsb1 As Date = Forms("订单信息").Controls("datetimepicker1").value
Dim tsb2 As Date = forms("订单信息").Controls("datetimepicker2").value
Dim tb1 As String = forms("订单信息").Controls("textbox1").text
Dim tb2 As String = forms("订单信息").Controls("textbox2").text

Dim sql As String
Dim ssql As String
If tsb1 = Nothing And tsb2 = Nothing Then
ssql = ""
Else
If tsb1 = Nothing Then
tsb1 = "1970-01-01"
End If

If tsb2 = Nothing Then
tsb2 = Date.Today.Date
End If
ssql = "入库日期>=\'" & tsb1 & "\' and 入库日期<=\'" & tsb2 & "\' "


End If
If tb2 = "" Then
sql = ssql
Else
sql = ssql & "and 订单状态 = \'" & tb2 & "\' "
End If
Dim dsql As String
If tb1="" Then
dsql=""
Else
dsql= " 商品条码 = \'" & tb1 & "\'"
End If
messagebox.show(sql)
Tables("订单信息_table1").filter = sql
Tables("订单信息_table2").Filter = dsql

最好时候也就写成这样,但是and通配符不支持前后为空

--  作者：有点蓝
--  发布时间：2016/12/6 11:23:00
--
参考：http://www.foxtable.com/webhelp/scr/1058.htm

--  作者：悠悠yo
--  发布时间：2016/12/6 11:36:00
--

以下是引用有点蓝在2016/12/6 11:23:00的发言：
参考：http://www.foxtable.com/webhelp/scr/1058.htm
谢谢

--  作者：悠悠yo
--  发布时间：2016/12/6 11:59:00
--

以下是引用有点蓝在2016/12/6 11:23:00的发言：
参考：http://www.foxtable.com/webhelp/scr/1058.htm
我写了一个,但是提示我"语法错误:“入库日期”运算符后缺少操作数。"

--  作者：有点色
--  发布时间：2016/12/6 12:57:00
--

Dim ssql As String = "1=1"
If tsb1 <> Nothing Then
    ssql = " and 入库日期 >= #" & tsb1 & "#"
End If

If tsb2 <> Nothing Then
    ssql = " and 入库日期 <= #" & tsb2 & "#"
End If

--  作者：悠悠yo
--  发布时间：2016/12/6 14:09:00
--

以下是引用有点色在2016/12/6 12:57:00的发言：

Dim ssql As String = "1=1"
If tsb1 <> Nothing Then
    ssql = " and 入库日期 >= #" & tsb1 & "#"
End If

If tsb2 <> Nothing Then
    ssql = " and 入库日期 <= #" & tsb2 & "#"
End If

With e.Form.Controls("datetimepicker1")
If .value IsNot Nothing Then
filter = "入库日期>=#" & tsb1 & "#"
ElseIf .value Is Nothing Then
filter = ""
End If
End With
messagebox.Show(filter)
With e.Form.Controls("datetimepicker2")
If .value IsNot Nothing Then
filter = filter & " and 入库日期<=#" & tsb2 & "#"
ElseIf .value Is Nothing Then
filter = filter
End If
End With
messagebox.Show(filter)
With e.Form.Controls("textbox1")
If .value IsNot Nothing Then
filter1 = "商品条码 = \'" & tb1 & "\'"
End If
End With
With e.Form.Controls("textbox2")
If .value IsNot Nothing Then
filter =filter & " and 订单状态 = \'" & tb2 & "\'"
ElseIf .value Is Nothing Then
filter = filter
End If
End With
messagebox.Show(filter)

Tables("订单信息_table1").filter = filter
Tables("订单信息_table2").Filter = filter1
还是and的问题

--  作者：有点蓝
--  发布时间：2016/12/6 14:27:00
--
如果不理解代码就按照帮助的来，不要乱改

Dim filter As String = "1=1"
With e.Form.Controls("datetimepicker1")
If .value IsNot Nothing Then
filter &= " and >=#" & .value & "#"
End If
End With
messagebox.Show(filter)
With e.Form.Controls("datetimepicker2")
If .value IsNot Nothing Then
filter &= " and 入库日期<=#" & .value & "#"
End If
End With
messagebox.Show(filter)
Dim filter1 As String = "1=1"
With e.Form.Controls("textbox1")
If .value IsNot Nothing Then
filter1 &= " and 商品条码 = \'" & tb1 & "\'"
End If
End With
With e.Form.Controls("textbox2")
If .value IsNot Nothing Then
filter1 &= " and 订单状态 = \'" & tb2 & "\'"
End If
End With
messagebox.Show(filter)

Tables("订单信息_table1").filter = filter
Tables("订单信息_table2").Filter = filter1

[此贴子已经被作者于2016/12/6 14:27:38编辑过]

--  作者：悠悠yo
--  发布时间：2016/12/6 15:37:00
--

以下是引用有点蓝在2016/12/6 14:27:00的发言：
如果不理解代码就按照帮助的来，不要乱改

Dim filter As String = "1=1"
With e.Form.Controls("datetimepicker1")
If .value IsNot Nothing Then
filter &= " and >=#" & .value & "#"
End If
End With
messagebox.Show(filter)
With e.Form.Controls("datetimepicker2")
If .value IsNot Nothing Then
filter &= " and 入库日期<=#" & .value & "#"
End If
End With
messagebox.Show(filter)
Dim filter1 As String = "1=1"
With e.Form.Controls("textbox1")
If .value IsNot Nothing Then
filter1 &= " and 商品条码 = \'" & tb1 & "\'"
End If
End With
With e.Form.Controls("textbox2")
If .value IsNot Nothing Then
filter1 &= " and 订单状态 = \'" & tb2 & "\'"
End If
End With
messagebox.Show(filter)

Tables("订单信息_table1").filter = filter
Tables("订单信息_table2").Filter = filter1

[此贴子已经被作者于2016/12/6 14:27:38编辑过]

谢谢,受教了,一开始我还不明白那个1=1是什么,看了两遍我理解了