Consulting

Results 1 to 7 of 7

Thread: Variable Fields Update in Parameterised Query

  1. #1
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location

    Variable Fields Update in Parameterised Query

    I have an Access database with an Excel front-end, and I can retrieve, insert, update, delete rows from the Access tables using data in Excel tables.

    I have it all working fine, but at present, the update query is written such that it actions all columns in the table. So for instance, in my Account table, I have to update the Date Opened, Date Closed, Account Number, etc., even though the only field changed is the date closed. I know I could write separate queries for each field, but this would mean adding a ton of extra code to the front-end, and it justs seems naff. I want one query that can update 1, 2, 3 or all fields depending upon what I supply. I can do this easily in SQL Server, but how do I do it with Access SQL.

    So I thought I would use a query that determined if a parameter was present at run time and update if this was the case.

    My first thought was an iif statement, like so

    PARAMETERS param.id Long, param.[date].opened DateTime, param.[date].closed DateTime;
    UPDATE Account 
    SET DateOpened = IIF(param.date.opened IS NOT NULL, param.date.opened), 
        DateClosed = IIF(param.date.closed IS NOT NULL,  param.date.closed)
    WHERE Id = param.id;
    This failed when one of the parameters was null, it set that date to null, not what I intended.


    Second attempt was to get the current value if the parameter was null, as in

    PARAMETERS param.id Long, param.[date].opened DateTime, param.[date].closed DateTime;
    UPDATE Account 
    SET DateOpened = IIF(param.date.opened IS NULL, (SELECT ac.DateOpened From Account AS ac WHERE Id = param.id), param.date.opened), 
        DateClosed = IIF(param.date.closed IS NULL, (SELECT ac.DateClosed From Account AS ac WHERE Id = param.id), param.date.closed)
    WHERE Id = param.id;
    This failed at runtime saying the 'Operation must be an updateable query'?


    I Googled this for help and saw an idea where the query had separate update statements for each field, so I tried this

    PARAMETERS param.id Long, param.[date].opened DateTime, param.[date].closed DateTime;
    
    UPDATE Account 
    SET DateOpened = param.date.opened 
    WHERE Id = param.id AND param.date.opened IS NOT NULL;
    
    UPDATE Account 
    SET DateClosed = param.date.closed 
    WHERE Id = param.id AND param.date.closed IS NOT NULL;
    but I got a compile error with 'Characters found after end of UPDATE statement', with the second UPDATE highlighted. Removing the semi-colon at the end of the first update created a syntax error in the first update statement.


    The first version is more elegant in my view, but any working version would be better than what I have at present. Anyone know what the query would look like to achieve this objective?
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  2. #2
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    I've never done Access so take this with a whole block of salt
    MyArr = Query Entire Record
    MyArr(n) = Date
    Update Entire Record = MyArr
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  3. #3
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Quote Originally Posted by SamT View Post
    I've never done Access so take this with a whole block of salt
    MyArr = Query Entire Record
    MyArr(n) = Date
    Update Entire Record = MyArr
    The whole point is to handle the variability via the SQL, application code is not the approach I want to take.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  4. #4
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    I asked this question on ListServ as people here seemed no more aware of how to do than I was, and I got a response in 5 minutes from the super-smart Rory Archibald. As he pointed out, I was well over-egging it, I more or less had it with the IIF solution but I added in a complication that stopped it working.

    This is the working solution

    PARAMETERS param.id Long, param.[date].opened DateTime, param.[date].closed DateTime;
    UPDATE Account 
    SET DateOpened = IIF(param.date.opened IS NULL, DateOpened, param.date.opened), 
        DateClosed = IIF(param.date.closed IS NULL, DateClosed, param.date.closed)
    WHERE Id = param.id;
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  5. #5
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    4,999
    Location
    So Bob the change was only in the "Is Not Null" to "Is Null"?
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  6. #6
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    No Ted, it was the second code sample, where I had a separate SELECT to try and get the present value, and all I had to do was use that field value. In pseudo code for one column

        FieldValue = IF param.for.that.field IS NULL Then FieldValue Else param.for.that.field ENDiF
    or as an actual column

        DateOpened = IIF(param.date.opened IS NULL, DateOpened, param.date.opened)
    Last edited by Bob Phillips; 03-24-2022 at 01:15 PM.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  7. #7
    VBAX Master Aflatoon's Avatar
    Joined
    Sep 2009
    Location
    UK
    Posts
    1,720
    Location
    Quote Originally Posted by Bob Phillips View Post
    the super-smart Rory Archibald
    There you go over-egging it again.
    Be as you wish to seem

Posting Permissions

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