- Foxtable(狐表) (http://foxtable.com/bbs/index.asp)
-- 专家坐堂 (http://foxtable.com/bbs/list.asp?boardid=2)
---- [求助]存在空值时，无法汇总 (http://foxtable.com/bbs/dispbbs.asp?boardid=2&id=177363)
-- 发布时间：2022/5/19 8:59:00
-- [求助]存在空值时，无法汇总
此主题相关图片如下：1.png
描述，汇总表是根据明细表中的 类别 、名称、规格型号、单位、不含税单价进行汇总的，但是，当明细表中，存在空值的单元格时，就会出现无法汇总的情况
求解决方案
此主题相关图片如下：2.png
此主题相关图片如下：2.png代码如下：
Dim arys As List(of String())
arys = DataTables("人材机明细").GetValues("类别|名称|规格型号|单位|不含税单价")
Dim n_count As Integer = arys.Count
Tables("材料汇总表").AddNew(n_count)
Dim In_row As DataRow
Dim I As Integer = 0
For Each ary As String() In arys
in_row = DataTables("材料汇总表").DataRows(i)
In_row("类别") = ary(0)
In_row("名称") = ary(1)
In_row("规格型号") = ary(2)
In_row("单位") = ary(3)
Dim price_string As String = ary(4)
Dim price As Double
If price_string = "" Then
price = 0
Else
price = CDbl(price_string)
End If
In_row("不含税单价") = price
In_row("净用量") = DataTables("人材机明细").Compute("sum(净用量)","类别 = \'"& ary(0) &"\' and 名称 = \'"& ary(1) &"\' and 规格型号 = \'"& ary(2) &"\' and 不含税单价 = \'"& price &"\' ")
In_row("实际用量") = DataTables("人材机明细").Compute("sum(实际用量)","类别 = \'"& ary(0) &"\' and 名称 = \'"& ary(1) &"\' and 规格型号 = \'"& ary(2) &"\' and 不含税单价 = \'"& price &"\' ")
Dim drs As List (of DataRow) = DataTables("人材机明细").Select("类别 = \'"& ary(0) &"\' and 名称 = \'"& ary(1) &"\' and 规格型号 = \'"& ary(2) &"\' and 不含税单价 = \'"& price &"\' ")
i = i + 1
Next
-- 发布时间：2022/5/19 9:06:00
--
if ary(2) > "" then
In_row("净用量") = DataTables("人材机明细").Compute("sum(净用量)","类别 = \'"& ary(0) &"\' and 名称 = \'"& ary(1) &"\' and 规格型号 = \'"& ary(2) &"\' and 不含税单价 = \'"& price &"\' ")
In_row("实际用量") = DataTables("人材机明细").Compute("sum(实际用量)","类别 = \'"& ary(0) &"\' and 名称 = \'"& ary(1) &"\' and 规格型号 = \'"& ary(2) &"\' and 不含税单价 = \'"& price &"\' ")
else
In_row("净用量") = DataTables("人材机明细").Compute("sum(净用量)","类别 = \'"& ary(0) &"\' and 名称 = \'"& ary(1) &"\' and 规格型号 is null and 不含税单价 = \'"& price &"\' ")
In_row("实际用量") = DataTables("人材机明细").Compute("sum(实际用量)","类别 = \'"& ary(0) &"\' and 名称 = \'"& ary(1) &"\' and 规格型号 is null and 不含税单价 = \'"& price &"\' ")
end if
如果【类别|名称|规格型号|单位|不含税单价】这些列都可能有空值，那就非常麻烦了。要做很多的判断。如果是这种情况个人的建议是不要有空值，使用“无规格型号”这种值来替代空值
-- 发布时间：2022/5/19 9:11:00
--
谢谢