INVALID SQL STATEMENT，EXPECTED'INSERT'，'DELETE'，'PROCEDURE'，'UPDATE' [英] INVALID SQL STATEMENT, EXPECTED 'INSERT', 'DELETE', 'PROCEDURE', 'UPDATE'

问题描述

选项比较数据库

Option Compare Database

Private Sub cmdOK_Click()

'' Pointer to error handler
    On Error GoTo cmdOK_Click_err
'' Declare variables
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim strBCCHNO As String
    Dim strBSIC As String
    Dim strSCLD As String
    Dim strSQL As String
'' Identify the database and assign it to the variable
    Set db = CurrentDb
'' Check for the existence of the query, create it if not found,
'' and assign it to the variable
    If Not QueryExists("Query1") Then
        Set qdf = db.CreateQueryDef("Query1")
    Else
        Set qdf = db.QueryDefs("Query1")
    End If
'' Get the values from the combo boxes
    If IsNull(Me.cbobcchno.Value) Then
        strBCCHNO = " Like ''*'' "
    Else
        strBCCHNO = "=" & Me.cbobcchno.Value & " "
    End If
    If IsNull(Me.cbobsic.Value) Then
        strBSIC = " Like ''*'' "
    Else
        strBSIC = "=" & Me.cbobsic.Value & " "
    End If
    If IsNull(Me.cboscld.Value) Then
        strSCLD = " Like ''*'' "
    Else
        strSCLD = "=''" & Me.cboscld.Value & "'' "
    End If
'' Build the SQL string
    strSQL = strSQL = "SELECT Query3.* " & _
         "FROM Query3 " & _
         "WHERE Query3.BCCHNO=" & Me.cbobcchno.Value & " " & _
         "AND Query3.SCLD=''" & Me.cboscld.Value & " '' " & _
         "AND Query3.BSIC=" & Me.cbobsic.Value & " ;"
'' Pass the SQL string to the query
    qdf.SQL = strSQL
'' Turn off screen updating
    DoCmd.Echo False
'' Check the state of the query and close it if it is open
    If Application.SysCmd(acSysCmdGetObjectState, acQuery, "Query1") = acObjStateOpen Then
        DoCmd.Close acQuery, "qryStaffListQuery"
    End If
'' Open the query
    DoCmd.OpenQuery "Query1"
cmdOK_Click_exit:
'' Turn on screen updating
    DoCmd.Echo True
'' Clear the object variables
    Set qdf = Nothing
    Set db = Nothing
    Exit Sub
cmdOK_Click_err:
'' Handle errors
    MsgBox "An unexpected error has occurred." & _
        vbCrLf & "Please note of the following details:" & _
        vbCrLf & "Error Number: " & Err.Number & _
        vbCrLf & "Description: " & Err.Description _
        , vbCritical, "Error"
    Resume cmdOK_Click_exit
End Sub

它还有一个模块，用于检查是否存在查询，代码为此如下所示：



选项比较数据库

It has also a module which Checks for the existence of a query, code for this is written below:

Option Compare Database

Public Function QueryExists(QueryName As String) As Boolean
'' Checks for the existence of a query (named as QueryName)
'' and returns true if query exists.
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    QueryExists = False
    Set db = CurrentDb
    For Each qdf In db.QueryDefs
        If qdf.Name = QueryName Then
            QueryExists = True
            Exit Function
        End If
    Next qdf
End Function

组合框从查询3中挑选数据.BCCHNO具有数值数据类型，BSIC也具有相同的数据类型。虽然SCLD本质上是字符串，但我的代码基于''BUILDING QUERY ON FLY'概念。

Combo boxes are picking data from query 3. BCCHNO has a numeric datatype, BSIC also has the same. While SCLD is string in nature, my code is based on ''BUILDING QUERY ON FLY'' concept.

推荐答案

我认为有太多要解释，但是。 。



其中一个原因是使用 IsNull [ ^ ]功能。请测试一下：

I think there is too many to explain, but..

One of the reasons is in using IsNull[^] function. Please test it:

Option Explicit

Sub TestIt()

Dim sTmp As String

sTmp = ""

MsgBox "Is null: " & IsNull(sTmp) & vbCr & "Is empty: " & IsEmpty(sTmp), vbInformation, sTmp

End Sub

请参考：

ComboBox对象[Access 2003 VBA语言参考] [ ^ ]

值属性[Access 2003 VBA语言参考] [ ^ ]



使用：

Please, refer this:
ComboBox Object [Access 2003 VBA Language Reference][^]
Value Property [Access 2003 VBA Language Reference][^]

Instead of IsNull function, use:

If Combobox1.Value = "" Then

或

or

If Len(Combobox1.Value) = 0 Then

顺便说一句：谁想使用 DAO [ ^ ]？使用 ADO [ ^ ]！

请参阅：使用Microsoft Access 2010进行数据编程 [ ^ ]。你会找到很多例子。



更多：

http://accessdatabasetutorial.com/tag/access-vba-2/ [ ^ ]

http ：//www.functionx.com/vbaccess/Lesson01.htm [ ^ ]

http://www.functionx.com/vbaccess/ Lesson26.htm [ ^ ]

http://allenbrowne.com/func-ado.html [ ^ ]

http://visualbasic.about.com/od/learnvbnetandadonet/l/aa050303a.htm [ ^ ]

http://visualbasic.about.com/od/learnvba/l/aa030803b.htm [ ^ ]

By The Way: Who wants to use DAO[^]? Use ADO[^]!
Please, see this: Data Programming with Microsoft Access 2010[^]. There you''ll find many examples.

More:
http://accessdatabasetutorial.com/tag/access-vba-2/[^]
http://www.functionx.com/vbaccess/Lesson01.htm[^]
http://www.functionx.com/vbaccess/Lesson26.htm[^]
http://allenbrowne.com/func-ado.html[^]
http://visualbasic.about.com/od/learnvbnetandadonet/l/aa050303a.htm[^]
http://visualbasic.about.com/od/learnvba/l/aa030803b.htm[^]

这篇关于INVALID SQL STATEMENT，EXPECTED'INSERT'，'DELETE'，'PROCEDURE'，'UPDATE'的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！