1

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.

        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,'

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]'

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

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!

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 " & _

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?

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.

Erik A
  • 31,639
  • 12
  • 42
  • 67
Ben.Name
  • 59
  • 1
  • 10
  • Possible duplicate of [Syntax error in insert into statement - what's the error?](https://stackoverflow.com/questions/43589800/syntax-error-in-insert-into-statement-whats-the-error) – Gustav Oct 26 '17 at 07:53
  • Please provide the query for your new issue (it looks like you haven't specified the parameters, and you're setting the same parameter `@DateRcvd` 3 times). Also note that `99/99/0000;0;` is an invalid date (every part of that date is invalid). The lowest available date in Access is `01-01-0100 00:00` – Erik A Oct 26 '17 at 14:05
  • You're setting `qdf2` here: `Set qdf2 = db.CreateQueryDef`, but then you're changing parameters on `qdf`, not `qdf2`. That's your error. Note that if you insert 0 into a date field, that gets cast to 30-12-899. You can use Null instead to leave it empty – Erik A Oct 26 '17 at 14:52
  • I don't believe I can up vote your comment as you already answered the main question. But This is another +1 as changing the parameters qdf revference was spot on! Also using Null instead of 0. Everyting works great now, Thank You. – Ben.Name Oct 26 '17 at 15:27

1 Answers1

1

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

        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.

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
Erik A
  • 31,639
  • 12
  • 42
  • 67
  • Thanks for the prompt reply! I am attempt to implement your solution, but keep getting compile error: Expected: expression it then highlights the code red and focuses on the first & on line db.CreateQueryDef("", & _ – Ben.Name Oct 26 '17 at 12:32
  • Please provide the line and implementation you used. – Erik A Oct 26 '17 at 12:33
  • I added the code to the original post..... stackoverflow comment box will not allow multiple "@" as it thinks I'm trying to notify multiple previous commenters. I understand "", & _ is to be blank Name , space , continue next line, and I copy/Pasted the line from your post and it still throws the error? – Ben.Name Oct 26 '17 at 13:01
  • Edited answer to avoid that error, see the current edit – Erik A Oct 26 '17 at 13:02
  • I up voted your answer. Thank You. Is there another way I should mark your response? This is the first answer I've received on Stackoverflow. Also I added an issue with attempting to use dates to the post, could you tell me if your solution can be modified or needs to be modified to handle dates? Thanks again for the Awesome help. :) – Ben.Name Oct 26 '17 at 13:57
  • See the help topic [What to do when someone answers my question](https://stackoverflow.com/help/someone-answers). – Erik A Oct 26 '17 at 14:01
  • Note that you lack the reputation to upvote my answer, but you can accept it – Erik A Oct 26 '17 at 14:07