时间戳

首页 > 解决方案 > VBA运行时错误而str sql分组一个值

sql - VBA运行时错误而str sql分组一个值

问题描述

我正在写下面的代码，但显示错误任何人都可以帮助我

在此处输入图像描述

Private Sub cmdShowData_Click()
    'populate Data
    strSQL = "SELECT [OverseasAgent],SUM([Weight]) As [Total] FROM [data$] WHERE  "


    If cmbNetwork.Text <> "" Then
        strSQL = strSQL & " Format([JobDate], 'MMM/YYYY')='" & cmbNetwork.Text & "'"
    End If

    If cmbOrigin.Text <> "" Then
        If cmbNetwork.Text <> "" Then
            strSQL = strSQL & " AND [Branch]='" & cmbOrigin.Text & "'"
        Else
            strSQL = strSQL & " [Branch]='" & cmbOrigin.Text & "'"
        End If
    End If

    If cmbDestination.Text <> "" Then
        If cmbNetwork.Text <> "" Or cmbOrigin.Text <> "" Then
            strSQL = strSQL & " AND [Destn]='" & cmbDestination.Text & "'"
        Else
            strSQL = strSQL & " [Destn]='" & cmbDestination.Text & "'"
        End If
    End If

    If cmdCountry.Text <> "" Then
        If cmbNetwork.Text <> "" Or cmbOrigin.Text <> "" Or cmbDestination.Text <> "" Then
            strSQL = strSQL & " AND [Country]='" & cmdCountry.Text & "'"
        Else
            strSQL = strSQL & " [Country]='" & cmdCountry.Text & "'"
        End If
    End If


    If cmbNetwork.Text <> "" Or cmbOrigin.Text <> "" Or cmbDestination.Text <> "" Or cmdCountry.Text <> "" Then
        'now extract data
        closeRS

        OpenDB

        rs.Open strSQL, cnn, adOpenKeyset, adLockOptimistic
        If rs.RecordCount > 0 Then
            Sheets("View").Visible = True
            Sheets("View").Select
            Range("dataSet").Select
            Range(Selection, Selection.End(xlDown)).ClearContents

           'Now putting the data on the sheet
            ActiveCell.CopyFromRecordset rs
        Else
            MsgBox "I was not able to find any matching records.", vbExclamation + vbOKOnly
            Exit Sub
        End If 
    End If
End Sub

标签： sqlexcelvba

解决方案

似乎"OverseasAgent"在SELECT查询开头"SELECT [OverseasAgent],SUM([Weight]) As [Total] FROM [data$] WHERE"，但它不是聚合函数的一部分SUM()。

尝试在没有SUM()最初的情况下更改查询以查看 yu 得到了什么。然后将其添加回来并添加Group By OversearsAgent零件。通常，请尝试在没有所有条件的情况下最初从表单运行硬编码查询。

为了准确了解查询出了什么问题，请在查询MsgBox strSQL之前编写rs.Open strSQL并查看查询的样子。然后进一步检查。

推荐阅读