Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2012

    Unanswered: Update query using newly updated values


    I am trying to use an update query to perform multiple updates at one time. For simplicities sake, I will use the most basic example here. [This problem could be solved for this specific example by creating another query to run afterwards, but with the more complex data, it is not practical to create endless queries.]

    My problem is this: I pull in a date based on other criteria and that updates fine, but I need the next five instances of that date which would be this calculated date +1, +2, etc. When I run the code it adds one, two, to the NON-updated date. I have to either loop it five times or make a new query that runs after the old data has been updated. Is there any way within a single query to have other fields update based on previously (within same query) updated fields? Hopefully this isn't too confusing, here is my code below.

    UPDATE CumulativeTable
    SET CumulativeTable.TrueUpYear_1 = IIf([Issue_Year]+5>[Val_Year],CumulativeTable.Issue_Year+5,IIf([Val_Month]>[Issue_Month],[Val_Year]+1,[Val_Year])),
    CumulativeTable.TrueUpYear_2 = [TrueUpYear_1]+1,
    CumulativeTable.TrueUpYear_3 = [TrueUpYear_2]+1,
    CumulativeTable.TrueUpYear_4 = [TrueUpYear_3]+1,
    CumulativeTable.TrueUpYear_5 = [TrueUpYear_4]+1;

    So I would like TrueUpYear_1 to update before TrueUpYear_2 is calculated, TrueUpYear_2 before TrueUpYear_3, etc.

    As I said, I know this is a somewhat simple fix to move 2-5 to another query, but there are much more complex versions of this project where each value is based on the previous so I really need to know how to get these to update before the next calculation.

    Thanks for your help!

  2. #2
    Join Date
    May 2004
    New York State
    There's nothing wrong with building a SQL statement in VBA, where code is concatenating a series of constants, numbers, variables, etc., and then running the SQL statement by itself.

    In other words, once you know the first updated date, you can update the other dates in a VBA procedure by building the SQL statement as a string (strSQL, for example) and then executing it using
    DoCmd****nSQL strSQL
    . You can even use Do While loops or For Next loops if it makes it easier.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts