ms-access - Access 时出现 MS ACCESS 错误

Question

我正在开发一个注册系统。但我收到此错误:You tried to execute a query that does not include the specified expression..
我正在使用以下代码:

Private Function RefreshAdvisoryList()

    Dim vRS As New ADODB.Recordset
    Dim sSQL As String

    'clear list
    txtsection.Clear

    'On Error GoTo ReleaseAndExit

        sSQL = "SELECT tblSection.SectionID, tblSection.SectionTitle, tblAdviser.SchoolYear, tblDepartment.DepartmentTitle, tblYearLevel.YearLevelTitle, tblAdviser.TeacherID" & _
                " FROM (tblDepartment INNER JOIN (tblYearLevel INNER JOIN tblSection ON tblYearLevel.YearLevelID = tblSection.YearLevelID) ON tblDepartment.DepartmentID = tblSection.DepartmentID) INNER JOIN tblAdviser ON (tblSection.SectionID = tblAdviser.SectionID) AND (tblDepartment.DepartmentID = tblAdviser.DepartmentID)" & _
                " GROUP BY tblSection.SectionID, tblSection.SectionTitle, tblAdviser.SchoolYear, tblDepartment.DepartmentTitle, tblYearLevel.YearLevelTitle, tblAdviser.TeacherID" & _
                " HAVING (((tblTeacher.TeacherID)='" & curTeacher.TeacherID & "') AND tblSection.SchoolYear='" & Me.txtSchoolYear.Text & "')" & _
                " ORDER BY tblAdviser.SchoolYear DESC;"


    If ConnectRS(con, vRS, sSQL) = False Then
        'fatal
        'temp
        MsgBox "Unable to connect Teacher's Advisory Recordset.", vbCritical
        'GoTo ReleaseAndExit
    End If

    If AnyRecordExisted(vRS) = True Then
        While vRS.EOF = False
        txtsection.AddItem vRS!SectionTitle
        vRS.MoveNext
   Wend
    End If

'Exit Function
'ReleaseAndExit:
'    Set vRS = Nothing
End Function

看看这个截图:

Answer 1

HAVING子句引用了这两个字段:

tblTeacher.TeacherID

tblSection.SchoolYear

SELECT字段列表包括:

tblAdviser.TeacherID

tblAdviser.SchoolYear

更改查询，以便所有对 TeacherID 的引用来自同一张 table 。对 SchoolYear 执行相同操作.

顺便说一句， tblTeacher甚至不包括在查询的数据源中。

如果可能，请启动 Access session 并使用查询设计器来构建此查询。它将帮助您避免此类错误。一旦你有一个在 Access 中工作的查询，然后调整你的代码以生成相同的工作 SQL 语句。