专家坐堂-[求助]sql空值和null的问题Foxtable(狐表) - 新一代数据库软件，完美融合Access、Foxpro、Excel、vb.net之优势，人人都能掌握的快速软件开发工具!

以文本方式查看主题

-  Foxtable(狐表)  (http://foxtable.net/bbs/index.asp)
--  专家坐堂  (http://foxtable.net/bbs/list.asp?boardid=2)
----  [求助]sql空值和null的问题  (http://foxtable.net/bbs/dispbbs.asp?boardid=2&id=135471)

--  作者：晨曦396
--  发布时间：2019/5/27 16:34:00
--  [求助]sql空值和null的问题

新增时其中一个字段没有填入值，sql中是null 不是空值，在执行筛选时，不能筛选出来，如果输入空格再清除空格就可以筛选，这个问题如何处理
Dim StartDate As Date
Dim EndDate As Date
StartDate =RibbonTabs("基础成本").Groups("查找").Items("开始日期").value
EndDate = RibbonTabs("基础成本").Groups("查找").Items("结束日期").value

Dim filter As String = "[inputdate] > = #" & StartDate & "# and [inputdate] < = #" & EndDate & "#"
Dim sts() As String = {"BCID","input","usercode","fullname"}
For Each st As String In sts
    Dim var As String = RibbonTabs("基础成本").Groups("查找").Items(st).text
    If var IsNot Nothing Then
        var = var.Replace("*","[*]")
        filter =filter & " and " & st & " Like \'*" & var & "*\'"
    End If
Next
Tables("Basecost").Filter = filter

--  作者：有点甜
--  发布时间：2019/5/27 17:21:00
--

Dim StartDate As Date
Dim EndDate As Date
StartDate =RibbonTabs("基础成本").Groups("查找").Items("开始日期").value
EndDate = RibbonTabs("基础成本").Groups("查找").Items("结束日期").value

Dim filter As String = "1=1"
If startDate <> Nothing Then
    filter &= " and [inputdate] >= #" & StartDate & "#"
End If
If EndDate <> Nothing Then
    filter &= " And [inputdate] <= #" & EndDate & "#"
End If
Dim sts() As String = {"BCID","input","usercode","fullname"}
For Each st As String In sts
    Dim var As String = RibbonTabs("基础成本").Groups("查找").Items(st).text
    If var IsNot Nothing Then
        var = var.Replace("*","[*]")
        filter =filter & " and " & st & " Like \'%" & var & "%\'"
    End If
Next
Tables("Basecost").Filter = filter

--  作者：晨曦396
--  发布时间：2019/5/28 10:39:00
--

以下是引用有点甜在2019/5/27 17:21:00的发言：

Dim StartDate As Date
Dim EndDate As Date
StartDate =RibbonTabs("基础成本").Groups("查找").Items("开始日期").value
EndDate = RibbonTabs("基础成本").Groups("查找").Items("结束日期").value

Dim filter As String = "1=1"
If startDate <> Nothing Then
    filter &= " and [inputdate] >= #" & StartDate & "#"
End If
If EndDate <> Nothing Then
    filter &= " And [inputdate] <= #" & EndDate & "#"
End If
Dim sts() As String = {"BCID","input","usercode","fullname"}
For Each st As String In sts
    Dim var As String = RibbonTabs("基础成本").Groups("查找").Items(st).text
    If var IsNot Nothing Then
        var = var.Replace("*","[*]")
        filter =filter & " and " & st & " Like \'%" & var & "%\'"
    End If
Next
Tables("Basecost").Filter = filter
这个是日期的，如果是下面的集合中的字段呢？比如
Dim sts() As String = {"BCID","input","usercode","fullname"} 这个位置的这些字段

--  作者：有点甜
--  发布时间：2019/5/28 10:49:00
--

1、msgbox(filter) 看弹出的条件是否正确。

2、试试

Dim StartDate As Date
Dim EndDate As Date
StartDate =RibbonTabs("基础成本").Groups("查找").Items("开始日期").value
EndDate = RibbonTabs("基础成本").Groups("查找").Items("结束日期").value

Dim filter As String = "1=1"
If startDate <> Nothing Then
    filter &= " and [inputdate] >= #" & StartDate & "#"
End If
If EndDate <> Nothing Then
    filter &= " And [inputdate] <= #" & EndDate & "#"
End If
Dim sts() As String = {"BCID","input","usercode","fullname"}
For Each st As String In sts
    Dim var As String = RibbonTabs("基础成本").Groups("查找").Items(st).text
    If var = Nothing Then
        \'不操作
    Else
        var = var.Replace("*","[*]")
        filter =filter & " and " & st & " Like \'%" & var & "%\'"
    End If
Next
Tables("Basecost").Filter = filter

--  作者：晨曦396
--  发布时间：2019/5/28 11:34:00
--
可以了谢谢！