Results 1 to 6 of 6
  1. #1
    laavista is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Jun 2010
    Posts
    42

    Multiple date variables in sql for currentdb.executive

    I need to update two fields in a table. I can successfully update the table if I update each field one at a time using the code:

    strSQL = "UPDATE tbl_rem set tbl_rem.(RemDate1] = #' & dtDate1 & "# where tbl_rem.[id] = (" & NumID & ");"

    strSQL = "UPDATE tbl_rem set tbl_rem.(RemDate2] = #' & dtDate2 & "# where tbl_rem.[id] = (" & NumID & ");"

    There has to be a way to update multiple values, but I can't get anything to work nor can I find on-line help.



    Your help would be GREATLY appreciated!

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Generally, the syntax is:

    UPDATE TableName
    SET Field1 = 111, Field2 = 222
    WHERE...
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    laavista is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Jun 2010
    Posts
    42

    tried the suggestion, but couldn't get it to work

    Thanks for the suggestion, but I couldn't get it to work. I tried variations of the following:

    The two statements that do work 'separately' are:
    strSQL = "UPDATE tbl_rem set tbl_rem.(RemDate1] = #' & dtDate1 & "# where tbl_rem.[id] = (" & NumID & ");"

    strSQL = "UPDATE tbl_rem set tbl_rem.(RemDate2] = #' & dtDate2 & "# where tbl_rem.[id] = (" & NumID & ");"


    What I tried (among other things):
    strSQL = "UPDATE tbl_rem set tbl_rem.(RemDate1] = #' & dtDate1 & "#, set tbl_rem.(RemDate2] = #' & dtDate2 & "# where tbl_rem.[id] = (" & NumID & ");"

    Any suggestions?

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You didn't see me repeat the SET keyword, did you?

    Plus the single quotes probably need to be doubles.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    laavista is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Jun 2010
    Posts
    42

    It works!

    Thank you so much. It worked! (the single quotes were a typo as my code was on another computer and I ended up typing it in. I also had another typo).

    THANK YOU for taking the time to help me with this!!!

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    No problemo, glad we got it working.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Shell with Batch Variables
    By robbyaube in forum Programming
    Replies: 2
    Last Post: 12-19-2019, 11:06 AM
  2. Update sql using currentdb.execute
    By laavista in forum Access
    Replies: 13
    Last Post: 08-15-2011, 03:51 PM
  3. Replies: 4
    Last Post: 01-25-2010, 04:14 PM
  4. Report to display multiple records by date.
    By af01waco in forum Reports
    Replies: 1
    Last Post: 03-21-2009, 02:12 PM
  5. sql in vb variables
    By emilylu3 in forum Programming
    Replies: 3
    Last Post: 03-04-2006, 01:26 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums