sql

2024-07-10 10:38| 来源: 网络整理| 查看: 265

不支持Access 2010 VBA JOIN表达式时间：2014-03-17 18:11:33

标签： sql vba ms-access inner-join

我试图为Access编写一个函数。我有一些非常基本的编码技能，但几乎不了解VBA。当我尝试从更新查询中调用该函数时，我得到了不支持＆＃34; JOIN表达式＆＃34;错误，我无法弄清问题是什么。这是功能：

Function PrimaryCampus(stuPIDM As String, termCode As String) As String Dim seqNumb As Integer Dim seqNumbStore As Integer Dim campus As String Dim classCount1 As Integer Dim classCount2 As Integer Dim classCount3 As Integer Dim db As Database Dim rec As DAO.Recordset seqNumb = 0 classCount1 = 0 classCount2 = 0 classCount3 = 1 Set db = CurrentDb Do While (seqNumb < 7) Set rec = db.OpenRecordset("SELECT COUNT(SSBSECT_SEQ_NUMB) " & _ "FROM SATURN_SFRSTCR " & _ "INNER JOIN SATURN_SSBSECT " & _ "ON SFRSTCR_TERM_CODE = SSBSECT_TERM_CODE " & _ "AND SFRSTCR_CRN = SSBSECT_CRN " & _ "WHERE SFRSTCR_PIDM = '" & stuPIDM & "' " & _ "AND SFRSTCR_TERM_CODE = '" & termCode & "' " & _ "AND SSBSECT_SEQ_NUMB LIKE '" & seqNumb & "*';") classCount1 = rec.Fields(0) If (classCount2 < classCount1) Then classCount2 = classCount1 seqNumbStore = seqNumb ElseIf (classCount2 = classCount1) Then classCount3 = classCount3 + 1 End If seqNumb = seqNumb + 1 Loop If (classCount3 > 1) Then campus = CStr(classCount3) ElseIf (seqNumbStore = 0) Then campus = "Distance Learning" ElseIf (seqNumbStore = 1) Then campus = "Clarkston" ElseIf (seqNumbStore = 2) Then campus = "Dunwoody" ElseIf (seqNumbStore = 3) Then campus = "Decatur" ElseIf (seqNumbStore = 5) Then campus = "Newton" ElseIf (seqNumbStore = 6) Then campus = "Alpharetta" End If PrimaryCampus = campus rec.Close Set rec = Nothing Set db = Nothing End Function

我确定还有其他错误，因为我使用的代码我不完全理解，但在继续调试之前我需要修复select语句。任何帮助将不胜感激。

编辑：感谢所有帮助过我的人。这是代码的工作版本，对于像我这样的人来说，他是VBA功能的完全新手：

Function PrimaryCampus(stuPIDM As Long, termCode As String) As String Dim seqNumb As Integer Dim seqNumbStore As Integer Dim campus As String Dim classCount1 As Integer Dim classCount2 As Integer Dim classCount3 As Integer Dim db As Database Dim rec As DAO.Recordset seqNumb = 0 classCount1 = 0 classCount2 = 0 classCount3 = 1 Set db = CurrentDb Do While (seqNumb < 7) Set rec = db.OpenRecordset("SELECT COUNT(SSBSECT_SEQ_NUMB) AS CAMP_COUNT " & _ "FROM SATURN_SFRSTCR " & _ "INNER JOIN SATURN_SSBSECT " & _ "ON SATURN_SFRSTCR.SFRSTCR_TERM_CODE = SATURN_SSBSECT.SSBSECT_TERM_CODE " & _ "AND SATURN_SFRSTCR.SFRSTCR_CRN = SATURN_SSBSECT.SSBSECT_CRN " & _ "WHERE SFRSTCR_PIDM = " & stuPIDM & " " & _ "AND SFRSTCR_TERM_CODE = '" & termCode & "' " & _ "AND SSBSECT_SEQ_NUMB LIKE '" & seqNumb & "*' " & _ "AND (SFRSTCR_RSTS_CODE LIKE 'R*' OR SFRSTCR_RSTS_CODE LIKE 'W*') " & _ "AND SFRSTCR_CREDIT_HR >= 1") classCount1 = rec.Fields(0) If (classCount1 = 0) Then classCount1 = 0 ElseIf (classCount2 < classCount1) Then classCount2 = classCount1 seqNumbStore = seqNumb ElseIf (classCount2 = classCount1) Then classCount3 = classCount3 + 1 End If seqNumb = seqNumb + 1 Loop If (classCount3 > 1) Then campus = CStr(classCount3) ElseIf (seqNumbStore = 0) Then campus = "Distance Learning" ElseIf (seqNumbStore = 1) Then campus = "Clarkston" ElseIf (seqNumbStore = 2) Then campus = "Dunwoody" ElseIf (seqNumbStore = 3) Then campus = "Decatur" ElseIf (seqNumbStore = 5) Then campus = "Newton" ElseIf (seqNumbStore = 6) Then campus = "Alpharetta" End If PrimaryCampus = campus rec.Close Set rec = Nothing Set db = Nothing End Function

我必须解决的错误：

连接条件中的显式表名。 stuPidm是一个NUMBER（8）字段。最初，stuPIDM是一个整数，但是当我运行更新时，这导致类型转换失败查询。我没有意识到Access VBA中的Integer相当于一个其他语言的短整数，所以类型需要为Long。我还需要在循环的if语句中修复逻辑错误我没有处理0的计数。醇> 3 个答案:

答案 0 :(得分：2)

尝试显式引用连接中的表和字段，如下所示：

"SELECT COUNT(SSBSECT_SEQ_NUMB) " & _ "FROM SATURN_SFRSTCR " & _ "INNER JOIN SATURN_SSBSECT " & _ "ON SATURN_SFRSTCR.SFRSTCR_TERM_CODE = SATURN_SSBSECT.SSBSECT_TERM_CODE " & _ "AND SATURN_SFRSTCR.SFRSTCR_CRN = SATURN_SSBSECT.SSBSECT_CRN " & _ "WHERE SFRSTCR_PIDM = '" & stuPIDM & "' " & _ "AND SFRSTCR_TERM_CODE = '" & termCode & "' " & _ "AND SSBSECT_SEQ_NUMB LIKE '" & seqNumb & "*';"

答案 1 :(得分：0)

查看here，您似乎必须指定记录集类型，否则它会假设您尝试调用表而不是查询。尝试：

Set rec = db.OpenRecordset("SELECT COUNT(SSBSECT_SEQ_NUMB) " & _ "FROM SATURN_SFRSTCR " & _ "INNER JOIN SATURN_SSBSECT " & _ "ON SFRSTCR_TERM_CODE = SSBSECT_TERM_CODE " & _ "AND SFRSTCR_CRN = SSBSECT_CRN " & _ "WHERE SFRSTCR_PIDM = '" & stuPIDM & "' " & _ "AND SFRSTCR_TERM_CODE = '" & termCode & "' " & _ "AND SSBSECT_SEQ_NUMB LIKE '" & seqNumb & "*';", _ Type:=dbOpenDynamic)

答案 2 :(得分：-3)

据我所知，Access不允许在JOIN中使用两对字段，因此您需要将第二个字段移到WHERE子句中：

Set rec = db.OpenRecordset("SELECT COUNT(SSBSECT_SEQ_NUMB) " & _ "FROM SATURN_SFRSTCR " & _ "INNER JOIN SATURN_SSBSECT " & _ "ON SFRSTCR_TERM_CODE = SSBSECT_TERM_CODE " & _ "WHERE SFRSTCR_PIDM = '" & stuPIDM & "' " & _ "AND SFRSTCR_TERM_CODE = '" & termCode & "' " & _ "AND SSBSECT_SEQ_NUMB LIKE '" & seqNumb & "*'" & _ "AND SFRSTCR_CRN = SSBSECT_CRN " & ";")

在上面，条款"AND SFRSTCR_CRN = SSBSECT_CRN "已从JOIN移到WHERE。

【本文地址】

sql

sql

今日新闻

推荐新闻