将 VBA 中的 Empty/Null Date 变量传递给 SQL UPDATE 语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24559848/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Passing an Empty/Null Date variable in VBA to an SQL UPDATE statement
提问by mccreaVBA
I have an excel userform with various textboxes, some are fields to enter dates. The user can then save their entries.
我有一个带有各种文本框的 excel 用户表单，有些是用于输入日期的字段。然后用户可以保存他们的条目。
At this point, I connect to an access backend via an ADO connection. The values entered by a user are passes to an SQL string, e.g.
此时，我通过 ADO 连接连接到访问后端。用户输入的值被传递给一个 SQL 字符串，例如
strSQL = "UPDATE tblDECONVERSION_DATA SET tblDECONVERSION_DATA.Status = '" & NewBusiness_WorkQueue.Decon_CaseStatus & "', " & _
"tblDECONVERSION_DATA.DMS = '" & NewBusiness_WorkQueue.Decon_DMS & "', " & _
"tblDECONVERSION_DATA.DateRecieved = #" & Format(NewBusiness_WorkQueue.Decon_DateRecieved, "mm/dd/yyyy") & "#, " & _
"tblDECONVERSION_DATA.WireDate = #" & Format(NewBusiness_WorkQueue.Decon_WireDate, "mm/dd/yyyy") & "#, " & _
"tblDECONVERSION_DATA.LastEditXID = '" & CurrUser & "', tblDECONVERSION_DATA.LastEditDate = #" & Now & "# " & _
"WHERE (((tblDECONVERSION_DATA.CaseID)=" & ID & "));"
adoRecSet.Open Source:=strSQL, ActiveConnection:=dbconnect, CursorType:=adOpenDynamic, LockType:=adLockOptimistic
However, some of the date fields can be left blank, meaning for example the NewBusiness_WorkQueue.Decon_DateRecieved variable being empty. This causes a syntax error. How can I pass a Null or Empty date variable in the SQL statement that both VBA and the access database will accept?
但是，某些日期字段可以留空，这意味着例如 NewBusiness_WorkQueue.Decon_DateRecieved 变量为空。这会导致语法错误。如何在 VBA 和 access 数据库都接受的 SQL 语句中传递 Null 或 Empty 日期变量？
回答by Tim Williams
strSQL = "UPDATE tblDECONVERSION_DATA SET tblDECONVERSION_DATA.Status = '" & _
NewBusiness_WorkQueue.Decon_CaseStatus & "', " & _
"tblDECONVERSION_DATA.DMS = '" & NewBusiness_WorkQueue.Decon_DMS & "', " & _
"tblDECONVERSION_DATA.DateRecieved = " & _
DateOrNull(NewBusiness_WorkQueue.Decon_DateRecieved) & ", " & _
"tblDECONVERSION_DATA.WireDate = " & _
DateOrNull(NewBusiness_WorkQueue.Decon_WireDate) & ", " & _
"tblDECONVERSION_DATA.LastEditXID = '" & CurrUser & _
"', tblDECONVERSION_DATA.LastEditDate = #" & Now & "# " & _
"WHERE tblDECONVERSION_DATA.CaseID=" & ID & ";"
An example function:
一个示例函数：
Function DateOrNull(v) As String
Dim rv as String
If IsDate(v) Then
rv = " #" & Format(v, "mm/dd/yyyy") & "# "
Else
rv = " null "
End If
DateOrNull = rv
End Function
