MS-Access更新SQL不是空但是空白（！日期和数字字段！）

I have a few controls that are number and short date format in my tables also the date controls are masked to mm/dd/yyyy. Some of the fields that are loaded into the form are blank from the original table and so when executing the sql I am essentially evaluating the wrong thing whether Im checking for '' or Null. as '' fails as text for date number and the fields are not actually blank.

我的表格中有一些数字和短日期格式的控件,日期控件也被屏蔽为mm / dd / yyyy。加载到表单中的一些字段在原始表中是空白的,因此在执行sql时,我实际上是在评估错误的东西,无论是检查''还是Null。 as'作为日期编号的文本失败,字段实际上不是空白。

        strSQL4 = "UPDATE [tblDetails] SET " & _
            "[Proposed] = IIF(IsNull(" & Forms!frmEdit.txtProposed.Value & "),0," & Forms!frmEdit.txtProposed.Value & "), " & _
            "[Multi] = IIF(IsNull(" & Forms!frmEdit.txtMulitplier.Value & "),0," & Forms!frmEdit.txtMulitplier.Value & "), " & _
            "[Rational] = '" & Forms!frmEdit.txtRational.Value & "' " & _
            " WHERE [RNumber] = '" & Forms!frmEdit.cmbUpdate.Value & "'"
            Debug.Print strSQL4
        dbs.Execute strSQL4

ERROR 3075 Wrong number of arguments used with function in query expression 'IIF(IsNull(),0,'

ERROR 3075查询表达式'IIF(IsNull(),0,'中函数使用的参数数量错误

I also tried entering the field itself suggested from another site

我也试过从另一个网站进入该领域

        strSQL4 = "UPDATE [tblDetails] SET " & _
            "[Proposed] = IIF(" & Forms!frmEdit.txtProposed.Value & "='',[Proposed]," & Forms!frmEdit.txtProposed.Value & "), " & _
            " WHERE [RNumber] = '" & Forms!frmEdit.cmbUpdate.Value & "'"
            Debug.Print strSQL4
        dbs.Execute strSQL4

Same Error 3075 'IIF(IsNull(),0,[ProposedHrs]'

相同错误3075'IIF(IsNull(),0,[ProposedHrs]'

***also fails if I use the IIF(IsNull method as opposed to the =''

如果我使用IIF(IsNull方法而不是='',***也会失败

I did not paste an example of the dates failing, but is the same idea, not null but is blank, but cant seem to update back to blank again or even skip maybe?

我没有粘贴日期失败的例子,但是是相同的想法,不是空但是空白,但似乎不能再次更新回空白甚至可能跳过?

Thanks to anyone in advance.

提前感谢任何人。

Update-1 from attempting Erik Von Asmuth code <--Thanks btw!

尝试Erik Von Asmuth代码的Update-1 < - 谢谢btw!

Set qdf = db.CreateQueryDef("", & _
            "UPDATE [tblDetails] SET " & _
            "[Proposed] = @Proposed, " & _
            "[Multi] = @Multi, " & _
            "[Rational] = @Rational " & _
            "WHERE [RNumber] = @RNumber")

This portion is all red and the first "&" is highlighted after closing the notification window Compile error: Expected: expression

该部分全部为红色,关闭通知窗口后突出显示第一个“&”编译错误:预期:表达式

Update-2: I moved the update to the first line and it seems to be working. Set qdf = db.CreateQueryDef("", "UPDATE [tblDetails] SET " & _

更新2:我将更新移动到第一行,它似乎正在工作。设置qdf = db.CreateQueryDef(“”,“UPDATE [tblDetails] SET”&_

I am going to try this method with the dates fields next.

我将在接下来的日期字段中尝试此方法。

Update-3: when attempting the same parameterization with textbox's masked with 99/99/0000;0;_ I am receiving item not found in collection? I have checked the spelling several times and everything seems ok. I tried the following three formats so set parameter DateRcvd, can anyone comment if this is correct for a text box with dates?

更新-3:尝试使用99/99/0000蒙版的文本框进行相同的参数化时; 0; _我收到的集合中找不到项目?我已经多次检查拼写,一切似乎都没问题。我尝试了以下三种格式,所以设置参数DateRcvd,任何人都可以评论,如果这是正确的日期文本框?

qdf.Parameters("@DateRcvd") = IIf(Nz(Forms!frmEdit.txtDateRcvd.Value) = "", 0, Forms!frmEdit.txtDateRcvd.Value)
qdf.Parameters("@DateRcvd") = IIf(IsNull(Forms!frmEdit.txtDateRcvd.Value), 0, Forms!frmEdit.txtDateRcvd.Value)
qdf.Parameters("@DateRcvd") = IIf(Forms!frmEdit.txtDateRcvd.Value = "", 0, Forms!frmEdit.txtDateRcvd.Value)

Update-4:

Dim qdf2 As DAO.QueryDef
Set db = CurrentDb
Set qdf2 = db.CreateQueryDef("", "UPDATE [tblDetails] SET " & _
             "[DateReceived] = @DateRcvd " & _
             "WHERE [RNumber] = @RNumber")
             qdf.Parameters("@DateRcvd") = IIf(Nz(Forms!frmEdit.txtDateRcvd.Value) = "", 0, Forms!frmEdit.txtDateRcvd.Value)
             qdf.Parameters("@RNumber") = Forms!frmEdit.cmbUpdate.Value
             qdf2.Execute

Please Note text box txtDateRcvd has an Input Mask 99/99/0000;0;_ set within the properties of the textbox.

请注意文本框txtDateRcvd在文本框的属性中设置了输入掩码99/99/0000; 0; _。

1 个解决方案

#1

You should account for empty strings, and do that IIF statement in VBA instead of SQL:

您应该考虑空字符串,并在VBA而不是SQL中执行该IIF语句:

        strSQL4 = "UPDATE [tblDetails] SET " & _
        "[Proposed] = " & IIF(Nz(Forms!frmEdit.txtProposed.Value) = "",0,  Forms!frmEdit.txtProposed.Value) & ", " & _
        "[Multi] = " & IIF(Nz(Forms!frmEdit.txtMulitplier.Value) = "",0, Forms!frmEdit.txtMulitplier.Value) & ", " & _
        "[Rational] = '" & Forms!frmEdit.txtRational.Value & "' " & _
        " WHERE [RNumber] = '" & Forms!frmEdit.cmbUpdate.Value & "'"

Or better yet, do it properly and parameterize the whole update so you can't get these kind of errors or SQL injection.

或者更好的是,正确执行并参数化整个更新,这样您就无法获得这些错误或SQL注入。

Example of how to do it properly:

如何正确执行的示例:

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Set db = CurrentDb
Set qdf = db.CreateQueryDef("",  _
    "UPDATE [tblDetails] SET " & _
    "[Proposed] = @Proposed, " & _
    "[Multi] = @Multi, " & _
    "[Rational] = @Rational " & _
    "WHERE [RNumber] = @RNumber" 
)
qdf.Parameters("@Proposed") = IIF(Nz(Forms!frmEdit.txtProposed.Value) = "",0,  Forms!frmEdit.txtProposed.Value)
qdf.Parameters("@Multi") = IIF(Nz(Forms!frmEdit.txtMulitplier.Value) = "",0, Forms!frmEdit.txtMulitplier.Value)
qdf.Parameters("@Rational") = Forms!frmEdit.txtRational.Value
qdf.Parameters("@RNumber") = Forms!frmEdit.cmbUpdate.Value
qdf.Execute

#1