locked
Access 2010 - SQL Update Syntax RRS feed

  • Question

  • Hi, I'm a little confused on how to accomplish an update query.  I'm working in Access 2010.

    I have a table "tblTest" with 6 fields ( A, B, C, etc ).  There is data in fields A, B, C, D.  There is no data in fields E, and F. 

    I have a form "frmTest" that has 6 text input boxes that correspond to each field in the table. boxA, boxB, boxC, etc.  This form also has a button to run the update query.

    On the form, I would like to search the table based on the data entered in boxA, boxB, boxC, and boxD - and then update all records that match the query with the contents of box E and box F.  However if the contents of either boxE or boxF are left blank, I don't want field E or F to update the table with a null value.  In addition, I don't want the values entered in boxA, boxB, boxC, or boxD to update the table, because a user might use a wildcard value in their search.  Input boxes in the query's criteriea need to be Like "*" & [Form][frmTest][boxA] & "*" to allow for the wildcard searches.

    Any idea on how to write the sql statement for this Update Query?

    Any help is greatly appreciated.  Thank you much for reading!

    Friday, July 5, 2013 5:08 PM

Answers

  • No, don't put the single-quotes around the Form Control references. Delimiting them with single-quotes make them literal Strings which will not work as you want.  Basically, the Update Query works but it will not find any matching record to update (since there is no Field value that looks like the literal String '[Forms]![frmBatchUpdate]![boxA]') so it does not update any record.

    The saved Query [FieldZUpdateQuery] with the SQL String:

    UPDATE tblTest
    SET FieldZ = [Forms]![frmBatchUpdate]![boxZ]
    WHERE ( FieldA Like "*" & [Forms]![frmBatchUpdate]![boxA] & "*" ) AND 
    ( FieldB Like "*" & [Forms]![frmBatchUpdate]![boxB] & "*" ) AND 
    ( FieldC Like "*" & [Forms]![frmBatchUpdate]![boxC] & "*" ) AND 
    ( FieldD Like "*" & [Forms]![frmBatchUpdate]![boxD] & "*" ) AND 
    ( FieldE Like "*" & [Forms]![frmBatchUpdate]![cboboxE] & "*" ) AND 
    ( FieldF Like "*" & [Forms]![frmBatchUpdate]![cboboxF] & "*" ) AND 
    ( FieldG Like "*" & [Forms]![frmBatchUpdate]![cboboxG] & "*" );

    should work fine but you need to check:

    * Table name and all Field names are correct.

    * Form name and all Control names are correct.

    * Make sure the Form [frmBatchUpdate] is opened and there are values entered in the boxes referenced.

    * Make sure that you have some records in your Tables that will satisfy the criteria.

     

    It may be easier if you test with just 1 criterion for the moment, e.g. on the first criterion entered in BoxA.

     


    Van Dinh

    • Marked as answer by Dummy yoyo Thursday, July 18, 2013 6:38 AM
    Monday, July 8, 2013 4:47 PM

All replies

  • Assuming that they are Text Fields, you can use an UPDATE Query/SQL like:

    UPDATE tblTest
    SET E = [Forms]![frmTest]![boxE], 
      F = [Forms]![frmTest]![boxF]
    WHERE (Len(Trim([Forms]![frmTest]![boxE])) > 0) 
      And (Len(Trim([Forms]![frmTest]![boxF])) > 0)   
      And (A Like "*" & [Forms][frmTest][boxA] & "*") 
      And (B Like "*" & [Forms][frmTest][boxB] & "*")
      And (C Like "*" & [Forms][frmTest][boxC] & "*")
      And (D Like "*" & [Forms][frmTest][boxD] & "*")


    Van Dinh


    • Edited by Van Dinh Friday, July 5, 2013 5:46 PM Typos
    Friday, July 5, 2013 5:36 PM
  • First post an example of how data looks now and then using that sample how you want it to look after the update.

    Second create a select query with all fields, open in design view, click on VIEW pulldown and select SQL View, highlight all, copy, and paste in a post.


    Build a little, test a little

    Friday, July 5, 2013 5:39 PM
  • Hi, Thanks for your help!  I've tried the query posted, however I get a syntax error, stating "missing operator" and then it lists the contents of the where statement.  I have a semi-colon at the end of the statement, and there are two > operators in the statement.  Not sure what is wrong - it matches the solution provided above...

    I have another question about this update statement.  If boxE is left blank, and boxF has the value "completed" in it, will this update statement insert the value "completed" in field F, for all records that match the criteria set forth in boxA, boxB, boxC, and boxD?  I would like my query to be able to do that.  In the case above, I don't want to update field E, and I do want to update field F. 

    Thanks again for your help.  I'm learning!  albeit very slowly :)

    Friday, July 5, 2013 6:26 PM
  • Hi, Thanks for your help!  I've tried the query posted, however I get a syntax error, stating "missing operator" and then it lists the contents of the where statement.  I have a semi-colon at the end of the statement, and there are two > operators in the statement.  Not sure what is wrong - it matches the solution provided above...

    The semi-colon is not important.  I double checked my suggested SQL and all operators + parentheses are correct. Please copy and paste your actual SQL in your reply.

    >>I have another question about this update statement.  If boxE is left blank, and boxF has the value "completed" in it, will this update statement insert the value "completed" in field F, for all records that match the criteria set forth in boxA, boxB, boxC, and boxD?  <<

    No.  You asked "However if the contents of either boxE or boxF are left blank, I don't want field E or F to update the table with a null value."

    >>I would like my query to be able to do that.  In the case above, I don't want to update field E, and I do want to update field F.<<

    Why would you care about updating Field E to Null/empty String if the existing values of Field E are Null/empty String currently as per your description  "There is no data in fields E, and F. "?

    Let's solve one problem at a time...


    Van Dinh


    • Edited by Van Dinh Friday, July 5, 2013 7:02 PM Typos
    Friday, July 5, 2013 6:52 PM
  • UPDATE tblTest SET F = "completed" WHERE (Len(Trim([Forms]![frmTest]![boxE])) = 0)

    And (Len(Trim([Forms]![frmTest]![boxF])) = "completed")

    And (A Like "*" & [Forms]![frmTest]![boxA] & "*") And (B Like "*" & [Forms]![frmTest]![boxB] & "*") And (C Like "*" & [Forms]![frmTest]![boxC] & "*") And (D Like "*" & [Forms]![frmTest]![boxD] & "*")

    (Modified code from Van)

    Van you forgot the bangs in between your [Forms]![ftmTest]![...]

    This might have caused the syntax error if you just copied/pasted.


    Access 2010




    • Edited by UpTide Friday, July 5, 2013 7:06 PM
    Friday, July 5, 2013 6:54 PM
  • >>Van you forgot the bangs in between your [Forms]![ftmTest]![...]

    This might have caused the syntax error if you just copied/pasted.<<   

    Thanks, UpTide.

    I copied from Mike's description and added the "s" in [Forms] for all 6 references + the bang in 2 references but somehow forgot about the other 4.


    Van Dinh



    • Edited by Van Dinh Friday, July 5, 2013 7:05 PM Typos
    Friday, July 5, 2013 6:59 PM
  • I wondered how you made a mistake,
    anyway. My code (that I copied from Van that he seemed to copy from someone else) will only do this:

    If boxE is left blank, and boxF has the value "completed" in it, will this update statement insert the value "completed" in field F, for all records that match the criteria set forth in boxA, boxB, boxC, and boxD?

    <

    and nothing more.


    Access 2010


    • Edited by UpTide Friday, July 5, 2013 7:07 PM
    Friday, July 5, 2013 7:05 PM
  • Oops, I found my syntax mistake.  I had left a couple of !'s inbetween the forms and the controls.  :)  After testing the supplied query, it only worked when I had a value in both boxE and BoxF.  If boxE or BoxF are blank, the update does not occur.  That is not how I'd like the update to work. 

    I should add one thing to the discussion.  In reality, my application has 6 fields like fields E and F, that I would like to update, and only want to update those fields that do not have a null value.

     I'm wondering if I need to write some wort of IF/ElseIf statement in vba that says

    IF [Forms]![frmTest]![boxE] is not null

    Then run QueryE;

    Elseif [Forms]![frmTest]![boxF] is not null

    Then run QueryF;

    Elseif  [Forms]![frmTest]![boxG] is not null

    Then run QueryG;

    End #end of vba script

    QueryE would look like

    UPDATE tblTest
    SET E = [Forms]![frmTest]![boxE], 
    WHERE  (A Like "*" & [Forms][frmTest][boxA] & "*")
     
    And (B Like "*" & [Forms][frmTest][boxB] & "*")
     
    And (C Like "*" & [Forms][frmTest][boxC] & "*")
     
    And (D Like "*" & [Forms][frmTest][boxD] & "*")

    QueryF would look like

    UPDATE tblTest
    SET F = [Forms]![frmTest]![boxF],
    WHERE (A Like "*" & [Forms][frmTest][boxA] & "*")
     
    And (B Like "*" & [Forms][frmTest][boxB] & "*")
     
    And (C Like "*" & [Forms][frmTest][boxC] & "*")
     
    And (D Like "*" & [Forms][frmTest][boxD] & "*")

    and QueryG would look like

    UPDATE tblTest
    SET G = [Forms]![frmTest]![boxG],
    WHERE (A Like "*" & [Forms][frmTest][boxA] & "*")
     
    And (B Like "*" & [Forms][frmTest][boxB] & "*")
     
    And (C Like "*" & [Forms][frmTest][boxC] & "*")
     
    And (D Like "*" & [Forms][frmTest][boxD] & "*")

    I'm sure my vba is wrong, but I think you get the idea.  Is this the right way of going about this, or did the orginal answer look more correct?

    Thanks again!

    Friday, July 5, 2013 7:16 PM
  • it is important to have the bangs (!) in between these

    WHERE (A Like "*" & [Forms]![frmTest]![boxA] & "*")
     
    And (B Like "*" & [Forms]![frmTest]![boxB] & "*")
     
    And (C Like "*" & [Forms]![frmTest]![boxC] & "*")
     
    And (D Like "*" & [Forms]![frmTest]![boxD] & "*")

    The syntax for the IF statement goes something like:

    IF (statement) Then

    [code]

    End If

    Here is a link for more information about the IF

    http://msdn.microsoft.com/en-us/library/752y8abs.aspx


    Access 2010



    • Edited by UpTide Friday, July 5, 2013 7:32 PM
    Friday, July 5, 2013 7:26 PM
  • After testing the supplied query, it only worked when I had a value in both boxE and BoxF. 

    That's what you asked for originally.

    >>

    I should add one thing to the discussion.  In reality, my application has 6 fields like fields E and F, that I would like to update, and only want to update those fields that do not have a null value.

     I'm wondering if I need to write some wort of IF/ElseIf statement in vba that says

    IF [Forms]![frmTest]![boxE] is not null

    Then run QueryE;

    Elseif [Forms]![frmTest]![boxF] is not null

    Then run QueryF;

    Elseif  [Forms]![frmTest]![boxG] is not null

    Then run QueryG;

    End #end of vba script

    <<

    In this case, I would recommend constructing the UPDATE SQL String dynamically (i.e. by VBA) depending on the value of each TextBox boxE, boxF, boxG, ...

    At the end of the SQL String construction, the SQL String should only include those Fields whose the corresponding boxes are not Null in the SET clause. You can then execute the UPDATE SQL with the Execute Method (preferred) or RunSQL Method. 

    Execute 1 UPDATE Query/SQL is much more efficient than 6 UPDATE Queries/SQLs.


    Van Dinh

    Saturday, July 6, 2013 2:24 AM
  • Thanks Van Dinh, your suggestions have been very helpful.  I would like to run just one dynamically created query built via VBA, however I'm still not good enough at VBA to accomplish that on my own very quickly.  I will try, however, I think I need to get past my new error when I run the sql update query.  Here's my query.  One thing to note in this example that is different in the above examples.  Fields A-G are populated.  FieldZ is the field I'm trying to update.  When this is run, I get the error"Run Time Error '3061'  Too Few Parameters.  Expected 8.  I'm wondering if I need to encapsulate the form controls with either '', "", or () to make this work.  Once I can get past this error, I'll work on getting the VBA for a dynamically created sql statement.  I figure I need to build a basic query that works, before I try building a dynamic one.  THanks for taking a look at this.

    UPDATE tblTest
    SET FieldZ = [Forms]![frmBatchUpdate]![boxZ]
    WHERE ( FieldA Like "*" & [Forms]![frmBatchUpdate]![boxA] & "*" ) AND
    ( FieldB Like "*" & [Forms]![frmBatchUpdate]![boxB] & "*" ) AND
    ( FieldC Like "*" & [Forms]![frmBatchUpdate]![boxC] & "*" ) AND
    ( FieldD Like "*" & [Forms]![frmBatchUpdate]![boxD] & "*" ) AND
    ( FieldE Like "*" & [Forms]![frmBatchUpdate]![cboboxE] & "*" ) AND
    ( FieldF Like "*" & [Forms]![frmBatchUpdate]![cboboxF] & "*" ) AND
    ( FieldG Like "*" & [Forms]![frmBatchUpdate]![cboboxG] & "*" );

    All suggestions and feedback is welcome.

    Monday, July 8, 2013 2:04 PM
  • >>When this is run, I get the error"Run Time Error '3061'  Too Few Parameters.  Expected 8.<<

    That means you run the SQL by code and some of the Form Control references (actually all since the posted SQL has 8 Form Control references) are not resolved correctly.  Most likely, the problems occur in your SQL construction in code.

    Please post you VBA code if you need further help.

     

      


    Van Dinh

    Monday, July 8, 2013 2:24 PM
  • Hi, well I've gotten past the too few parameters error by putting single quotes around the form controls, like I've done for the following update query called "FieldZUpdateQuery":

    UPDATE tblTest
    SET FieldZ = '[Forms]![frmBatchUpdate]![boxZ]'
    WHERE ( FieldA Like "*" & '[Forms]![frmBatchUpdate]![boxA]' & "*" ) AND
    ( FieldB Like "*" & '[Forms]![frmBatchUpdate]![boxB]' & "*" ) AND
    ( FieldC Like "*" & '[Forms]![frmBatchUpdate]![boxC]' & "*" ) AND
    ( FieldD Like "*" & '[Forms]![frmBatchUpdate]![boxD]' & "*" ) AND
    ( FieldE Like "*" & '[Forms]![frmBatchUpdate]![cboboxE]' & "*" ) AND
    ( FieldF Like "*" & '[Forms]![frmBatchUpdate]![cboboxF]' & "*" ) AND
    ( FieldG Like "*" & '[Forms]![frmBatchUpdate]![cboboxG]' & "*" );

    Here's the VBA that I used to call this one.

    Private Sub cmdBatchUpdate_Click()
    If Not IsNull(Me.boxZ) Then
    CurrentDb.Execute "FieldZUpdateQuery"
    ElseIf Not IsNull(Me.boxY) Then
    CurrentDb.Execute "FieldYUpdateQuery"
    ElseIf Not IsNull(Me.boxX) Then
    CurrentDb.Execute "FieldXUpdateQuery"
    ElseIf Not IsNull(Me.boxW) Then
    CurrentDb.Execute "FieldWUpdateQuery"
    ElseIf Not IsNull(Me.boxV) Then
    CurrentDb.Execute "FieldVUpdateQuery"
    ElseIf Not IsNull(Me.boxU) Then
    CurrentDb.Execute "FieldUUpdateQuery"
    Else
    MsgBox "Update Completed"
    End If

    End Sub

    For some reason, I get no indication that the update query has worked.  The msgbox does not pop up, nor does the data get updated when it runs FieldZUpdateQuery ( posted above ).  I don't even get an error message, which is why I added the message box to see if the code is running through the if/elseif statements.  By the way, when I ran this query, I made sure box's U, V, W, X, and Y values were null, so in theory the first statement in the vba code would be the only statement executed.

    Thanks again for looking at this.  You have been very helpful - thank you!

    Monday, July 8, 2013 3:10 PM
  • No, don't put the single-quotes around the Form Control references. Delimiting them with single-quotes make them literal Strings which will not work as you want.  Basically, the Update Query works but it will not find any matching record to update (since there is no Field value that looks like the literal String '[Forms]![frmBatchUpdate]![boxA]') so it does not update any record.

    The saved Query [FieldZUpdateQuery] with the SQL String:

    UPDATE tblTest
    SET FieldZ = [Forms]![frmBatchUpdate]![boxZ]
    WHERE ( FieldA Like "*" & [Forms]![frmBatchUpdate]![boxA] & "*" ) AND 
    ( FieldB Like "*" & [Forms]![frmBatchUpdate]![boxB] & "*" ) AND 
    ( FieldC Like "*" & [Forms]![frmBatchUpdate]![boxC] & "*" ) AND 
    ( FieldD Like "*" & [Forms]![frmBatchUpdate]![boxD] & "*" ) AND 
    ( FieldE Like "*" & [Forms]![frmBatchUpdate]![cboboxE] & "*" ) AND 
    ( FieldF Like "*" & [Forms]![frmBatchUpdate]![cboboxF] & "*" ) AND 
    ( FieldG Like "*" & [Forms]![frmBatchUpdate]![cboboxG] & "*" );

    should work fine but you need to check:

    * Table name and all Field names are correct.

    * Form name and all Control names are correct.

    * Make sure the Form [frmBatchUpdate] is opened and there are values entered in the boxes referenced.

    * Make sure that you have some records in your Tables that will satisfy the criteria.

     

    It may be easier if you test with just 1 criterion for the moment, e.g. on the first criterion entered in BoxA.

     


    Van Dinh

    • Marked as answer by Dummy yoyo Thursday, July 18, 2013 6:38 AM
    Monday, July 8, 2013 4:47 PM