代码之家 › 专栏 › 技术社区 › BioXhazard

无效参数错误:MSAccess和SQL

access-vba vba ms-access

BioXhazard · 技术社区 · 16 年前

Set rs = CurrentDb.OpenRecordset("SELECT TimeID " & _
    "FROM tblLunchTime " & _
    "WHERE ProductionID = prodSelect AND EndTime is NULL AND StartTime < dateAdd('h', 3, NOW())", [dbSeeChanges])

这里面有什么不对的吗?

Private Sub cmdClockEnd_Click()

'Check if a group has been selected.
If frmChoice.value = 0 Then

    MsgBox "Please select a production line."

    End

End If

'Setup form for user input.
lblEnd.Visible = True

'Save end of lunch value.
lblEnd.Caption = Format(Now, "MMM/DD/YY hh:mm:ss AMPM")

'Declare database variables.
Dim dbName As DAO.Database
Dim strValuesQuery As String
Dim rs As DAO.Recordset
Dim prodSelect As String
Dim sSQL As String
Dim timeValue As String
Set dbName = CurrentDb

'Get values of Production Line.
If frmChoice.value = 1 Then

prodSelect = "L2"

ElseIf frmChoice.value = 2 Then

prodSelect = "L3"

End If

'Get the last TimeID with the following parameters.
sSQL = "SELECT TimeID " & _
    "FROM tblLunchTime " & _
    "WHERE ProductionID = prodSelect AND EndTime is NULL AND StartTime < #" & DateAdd("h", 3, Now()) & "#"

Set rs = dbName.OpenRecordset(sSQL, dbSeeChanges)

strValuesQuery = _
                    "UPDATE tblLunchTime " & _
                    "SET EndTime = '" & Now & "'" & _
                    "WHERE TimeID = " & rs![TimeID] & " "

'Turn warning messages off.
DoCmd.SetWarnings False

'Execute Query.
DoCmd.RunSQL strValuesQuery

'Turn warning messages back on.
DoCmd.SetWarnings True

End Sub

3 回复 | 直到 16 年前

HansUp 16 年前

似乎对tblLunchTime有些困惑。。。它是本机Jet/ACE表还是指向另一个数据库中的表的链接。请显示此命令的输出:

Debug.Print CurrentDb.TableDefs("tblLunchTime").Connect

您可以将该行粘贴到即时窗口中,然后按enter键显示响应(您可以使用CTRL+g组合键打开即时窗口。)

ODBC数据库

Set rs = CurrentDb.OpenRecordset(sSQL, dbOpenDynaset, dbSeeChanges)

更新 :现在您已经越过了这个障碍,建议您使用UPDATE语句更改方法。不要关闭警告;请尝试以下操作:

'Execute Query. '
CurrentDb.Execute strValuesQuery, dbFailOnError

并添加一个错误处理程序来处理dbFailOnError捕获的任何错误。

Fionnuala 16 年前

您需要将prodSelect放在引号之外:

"WHERE ProductionID = " & prodSelect & " AND ...

几乎总是最好说:

sSQL="SELECT TimeID " & _
    "FROM tblLunchTime " & _
    "WHERE ProductionID = " & prodSelect & _
    " AND EndTime is NULL AND StartTime < dateAdd('h', 3, NOW())"
''Debug.print sSQL
Set rs = CurrentDb.OpenRecordset(sSQL)

啊哈,这是文本!你需要报价!

sSQL="SELECT TimeID " & _
    "FROM tblLunchTime " & _
    "WHERE ProductionID = '" & prodSelect & _
    "' AND EndTime is NULL AND StartTime < dateAdd('h', 3, NOW())"

David-W-Fenton 16 年前

我想我也应该在客户端执行日期标准连接,因为还有一件事可能出错:

  "...StartTime < #" & DateAdd("h", 3, Now()) & "#"

我不知道SQL Server没有DateAdd()和Now()函数,也不知道它们的行为与Access中的行为不完全相同,但我不会冒险--我会在客户机上执行此计算,而不是将其交给服务器。