主题：按时间段统计数值

现在应该是次数等于1对两列统计数值都产生影响了，我的要求是次数等于1只对bl列的求和产生影响。

改为使用表达式列b.Totals.Addexp：http://www.foxtable.com/webhelp/topics/2960.htm

b.Totals.Addexp("bl", "iif(次数=1,bl,0)")  改成这样运行提示错误
此主题相关图片如下：qq截图20231010141721.png

如果是SqlServer改为case when

b.Totals.Addexp("bl", "case 次数 when 1 then bl else 0")     

 

这么写不对，怎么改？

b.Totals.Addexp("bl", "case when  次数=1 then bl else 0")

Dim b As New SQLGroupTableBuilder("统计表1", "操作表")
b.C
b.AddTable("操作表", "sureyid", "主表", "sureyid") '添加统计表
b.Groups.AddDef("cargo") '根据产品名称分组
b.Totals.AddDef("weightofcargo") '对数量进行统计
'b.Totals.AddDef("bl") '对数量进行统计
b.Totals.Addexp("bl", "case when  次数=1 then bl else 0")                
Dim filter As String

With e.Form.Controls("StartDate1")
    If .Value IsNot Nothing Then
        Filter = "日期 >= '" & .Value & "'"
    End If
End With
With e.Form.Controls("EndDate1")
    If .Value IsNot Nothing Then
        If Filter > "" Then
            Filter = Filter & " And "
        End If
        Filter = Filter & "日期 <= '" & .Value & "'"
    End If
End With
With e.Form.Controls("货物")
    If .Value IsNot Nothing Then
        If Filter > "" Then
            Filter = Filter & " And "
        End If
        Filter = Filter & "[cargo]= '" & .Value & "'"
    End If
End With
With e.Form.Controls("船代")
    If .Value IsNot Nothing Then
        If Filter > "" Then
            Filter = Filter & " And "
        End If
        Filter = Filter & "[company]= '" & .Value & "'"
    End If
End With

'b.filter = "日期>='01/05/2023' and 日期<='08/09/2023' and [cargo] = '煤'"
If Filter > "" Then

    b.filter = filter
   
    Tables("主表_Table5").DataSource = b.BuildDataSource()
    ' Maintable = Tables("统计表1") '打开生成的统计表
    'b.Build '生成统计表

Else
    messagebox.Show("请填入选项再搜索")
End If

 

还是提示同样错误呢？

b.Totals.Addexp("bl", "case when  次数=1 then bl else 0 end")